Answered by:
working with connected tables

Question
-
Hello. In a lookup tabel I have this formula.
=if([WO Type]="Cashout",[Work Order Date],Blank())
All of these are in the lookup table. What I want to do is add a condition based on the data table connected to the lookup table, to say only return the work order date, if any of the invoices (in the non lookup data table) attached to the work order, have a paid status. I used the formula below to reference the relatedtable but not sure how to adapt that for formula above. Thanks.
=sumx(relatedtable('Big Invoices My Baby'),[Invoice Dollars Status Paid])
Wednesday, September 14, 2016 6:31 PM
Answers
-
Hi AlexMartini,
I am trying to reproduce your issue in my local environment using following three linked tables.
In Claims table, to get the sum of paid with a particular status, you can create calculated column using the following formula and get the expected results.
In addition, the first formula, it will returns [Work Order Date] when [WO Type], otherwise it will returns Blank. The second formula means you will calculate the sum of [Invoice Dollars Status Paid] in related table “Big Invoices My Baby”. What’s the relationship between them, please give me more details and sample data structure.Regards,
Angelia- Edited by Angelia ZhangMicrosoft contingent staff Friday, September 16, 2016 10:01 AM
- Proposed as answer by Angelia ZhangMicrosoft contingent staff Tuesday, September 20, 2016 2:26 PM
- Marked as answer by Charlie Liao Wednesday, September 21, 2016 2:45 AM
Friday, September 16, 2016 10:01 AM -
You've already found the RELATEDTABLE function, this returns a table that you can work with in other functions, e.g.
FILTER(RELATEDTABLE(Invoices), Invoices[Status]="Paid")
this will only return the related rows with status paid. Having at least one paid invoice can be checked by counting the rows in this table with COUNTROWS:
IF(COUNTROWS(FILTER(RELATEDTABLE(Invoices), Invoices[Status]="Paid"))>0, ... )
- Proposed as answer by Angelia ZhangMicrosoft contingent staff Monday, September 19, 2016 9:12 AM
- Marked as answer by Charlie Liao Wednesday, September 21, 2016 2:45 AM
Friday, September 16, 2016 9:45 PMAnswerer
All replies
-
Hi AlexMartini,
I am trying to reproduce your issue in my local environment using following three linked tables.
In Claims table, to get the sum of paid with a particular status, you can create calculated column using the following formula and get the expected results.
In addition, the first formula, it will returns [Work Order Date] when [WO Type], otherwise it will returns Blank. The second formula means you will calculate the sum of [Invoice Dollars Status Paid] in related table “Big Invoices My Baby”. What’s the relationship between them, please give me more details and sample data structure.Regards,
Angelia- Edited by Angelia ZhangMicrosoft contingent staff Friday, September 16, 2016 10:01 AM
- Proposed as answer by Angelia ZhangMicrosoft contingent staff Tuesday, September 20, 2016 2:26 PM
- Marked as answer by Charlie Liao Wednesday, September 21, 2016 2:45 AM
Friday, September 16, 2016 10:01 AM -
You've already found the RELATEDTABLE function, this returns a table that you can work with in other functions, e.g.
FILTER(RELATEDTABLE(Invoices), Invoices[Status]="Paid")
this will only return the related rows with status paid. Having at least one paid invoice can be checked by counting the rows in this table with COUNTROWS:
IF(COUNTROWS(FILTER(RELATEDTABLE(Invoices), Invoices[Status]="Paid"))>0, ... )
- Proposed as answer by Angelia ZhangMicrosoft contingent staff Monday, September 19, 2016 9:12 AM
- Marked as answer by Charlie Liao Wednesday, September 21, 2016 2:45 AM
Friday, September 16, 2016 9:45 PMAnswerer