0

1 unique field from 3 tables

Michelle Leach

March 29, 2022 13:43

None

Follow

Hi

I'm building a job cost report and require the following on one row, with the unique ID being 'Cost Code code':-

• Sum of 'Total Cost' from Job Ledger Entry table
• Sum of 'Committed Amount' from Purchase Line table
• Sum of 'Sub. Committed Amount' from Subcontract Order table

I only wish to see the cost code once with all three values against it.

This is obviously an additional module added on for construction but the methodology remains the same.

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Michelle Leach March 29, 2022 13:44

  Sorry, meant to add:-

  I'm struggling to find a way of all obtaining the one field, being Cost code code from 3 tables in one cell for all sums to refer to....

• 0
  
  CascadiaDataworks April 18, 2022 16:13

  There are two ways to think about it.

  1.  Typically you want to pull the unique ID from the table where all the values exist and has all the necessary filters.  I'm not familiar with this Unique ID specifically, but is there one of those tables that has the value and filters you need?  If not, you can use Array Calculations to build the replicator.

  2. If you need to pull all the unique IDs that are found in each of the tables to create one list, you can use NL(Filter) and NP(Union) to find and combine the values.  Might look something like:

  a. NF(Filter, Job Ledger Entry, UniqueID, FilterField1, Filter1,...)
  b. NF(Filter, Purchase Line Table, UniqueID, FilterField1, Filter1,...)
  c. NF(Filter, Subcontract Order, UniqueID, FilterField1, Filter1,...)

  d. NL(Rows,NP(Union, Reference to A, Reference to B, Reference to C)

  This would create a unique list of unique IDs from each of the tables into one list.