locked
working with connected tables RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Friday, September 16, 2016 10:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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, ... )

    Friday, September 16, 2016 9:45 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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


    Friday, September 16, 2016 10:01 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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, ... )

    Friday, September 16, 2016 9:45 PM
    Answerer