Hello!
I am trying to join two tables but it seems not to be so easy in that case.
There are three keys to be joined. Some lines are inexistant in one table or another.
What I would like to see is a new table (or a table grouped from one of the two existing tables) with the 3 keys (Product, Year, Financeur) and all details from the two tables (Value A and Value B).
I made a mock-up of my problem here: Two table joint - Grist
Any clue how I could do that?
Hello, I see only 2 tables.
But let me explain what I have and how I solve this.
I have client, for which I make yearly fiscal statements and within this year I have at least 4 vat records. In the vat record I have a field to connect to year and to client. In year I have a fiield to connect to client.
In year I have created fields (formula’s) that summarize the vat records of that year. Within client I can do the same. In my client account I can see all connected tables.
Is this helpfull?
Hello @hera and thank you for taking the time.
I am not sure to see how this could help. Maybe I did not understand properly your configuration.
What I tried in the two group tabled in the link I sent is to visualize all data in one table. Howerver, we can easily see that some are lacking. For example, in the “Financements” group table, it does not show any data for Product 2 in 2024 even if the “Besoin global” table has this data.
In my more complex use case, there is a Product table and the products columns of the two “Financements” and “Besoin global” tables are Reference columns to the Product table. Same for the “Financeur” columns but not (yet?) for the “year” columns.
My clients table is your product table, my year table is your besoin global table and my VAT table is your financements table.
Financements should have a reference to besoin global. In my years table (besoin global) I have a formulafield where I sum all VAT’s (Financement) with “SUM(Vat_returns.lookupRecords(years_id=$id).Omzet2)” for you that could be SUM(Financements.lookupRecords(besoin_global_id=$id).ValueA). Or a link to the summarized fields (which would save time if the table is large)
yes ok that is a way which is interesting, thank you.
I would have liked to have a solution where I don’t need to modify the entire Financements table nor force the user to make the link itself.
In financement create a referencefield to besoin global. In that referencefield use the lookuprecords function to search for the field.
Well indeed this is what I already tried (adding a summary table is equivalent, see the modification I made to the sample file). But it won’t create the full summary table of all the lines I want. In my case, I wish I had a summary table with all data. I created a fake table (manually) to see what I try to get: Two table joint - Grist
I see the issue. I thought that Two-way references would solve the tric, but those are not currently supported for Formula or Trigger Formula columns. But for this I would (now) solve this with an additional widget such as I created in you sample and I added the sum (if the database is large I would recommend to use a lookup to the summarized table).
Alright, good idea, still manual correction but easy and quick! Thank you @hera for the help you gave me!