I am trying to fill a field with data from another table - however when I do this using Table[Field] in Custom Column my file goes into meltdown and turns from a 500kb file to one of many many Mb - so must be doing something wrong.
So I have a table of invoices which show the Date Due and the period in which the invoice was raised. The data table is an historical data snapshot - so each period I get new data. But rather than hard-coding when the data relates to I want to use a query to work it out. (also need to learn how to post nice data!)
Below is a very brief extract of the tables - this is the main data table
Period Customer Due Date Amount Calc Days Over
I then have a table which will give me the effective due date based on the Period
PeriodAbs Due Date
So Looking at my main data table I can see that the most recent period for which I have data is 202102 - so that tells me I need to use 31/05/2021 as my Calc date - I get this by duplicating my data table, stripping out all columns except for period, remove duplicates, sort by Period and remove all but the first entry - so I get the following table
What I was doing was then adding this field to my main data table (for all rows) using a custom column and then trying to use a Merge to bring in the Abs Due Date into the Table so I can then calculate exactly how many days over each invoice is by subdtracting the Abs Due Date form the Due Date
But when I do this my Query goes into a melt down - takes for ever to evaluate and grows into many many MB!
I think what I am doing is quite simple - but clearly I am going about it wrong - still a relative newbie!!