Skip to main content

How to use formulas to extract all fields from linked records directly

  • August 4, 2024
  • 2 replies
  • 132 views
RockyCCChong
Forum|alt.badge.img+12

I want to create a summary of my table row, which has linked records to them. Ideally, the entire summary in text, with the linked records expressed in text within the entire summary. An example below:

Case Table
Case |  Person  |  Invoice  
1234 |  Michael |  XYZ

Person Table
Name    | DOB      | Address
Michael | 1/1/1990 | 10 Oxley Road

Invoice Table
ID    | Items
XYZ   | Hamburger; Fries; Coke

Desired output:
1234; Michael;1/1/1990;10 Oxley Road; XYZ; Hamburger; $2; Fries; $2; Coke; $2

Ideally what I want to achieve is:
Case: 1234
Person: Michael
Invoice: XYZ
Line items
1. Hamburger \t\t $2
2. Fries \t\t $2
3. Coke \t\t $2
Total $6.



Apologies for the lame example for hamburger and coke...
I consider myself less than an expert, so if you do have better suggestions I am open to hear them. 

 

2 replies

Sistema_Aotearo
Forum|alt.badge.img+20

This shouldn't be hard to do.

It might not need to be a separate table, but you can if you'd like. This looks like a Case Summary so it could just be a formula field that lives on the Case Table. If you want it all separate, simply link the Case you want to summarize to the Summary Table.

Step 1 is to make a whole lot of "Lookup Field"s in your Case Table. Looking up details for each link.

Eg: "DOB" from Person Table; "Address" from Person Table; "Items" from Invoice Table

Step 2 is to make a formula field

CONCATENATE({Case}&"; "&{Person}&"; "&{"DOB" from Person Table}&"; "&{"Address" from Person Table}&"; "&{Invoice}&"; "&{"Items" from Invoice Table})
Justin NgProgramme Coordinator at Sistema Aotearoahttps://www.sistemaaotearoa.org.nz/
RockyCCChong
RockyCCChong
Forum|alt.badge.img+12
  • RockyCCChong
  • Author
  • Known Participant
  • August 7, 2024

This shouldn't be hard to do.

It might not need to be a separate table, but you can if you'd like. This looks like a Case Summary so it could just be a formula field that lives on the Case Table. If you want it all separate, simply link the Case you want to summarize to the Summary Table.

Step 1 is to make a whole lot of "Lookup Field"s in your Case Table. Looking up details for each link.

Eg: "DOB" from Person Table; "Address" from Person Table; "Items" from Invoice Table

Step 2 is to make a formula field

CONCATENATE({Case}&"; "&{Person}&"; "&{"DOB" from Person Table}&"; "&{"Address" from Person Table}&"; "&{Invoice}&"; "&{"Items" from Invoice Table})

Hi @Sistema_Aotearo Justin, 

I took your suggestion and worked on it. In the end it did solve my problem. How I did it is that I used a concatenate function at the lowest level table. In this case it would append the information at that level. 

On the higher level table I used a rollup function that uses an Arrayjoin to mash all the connected items together. 

 

 

Sistema_Aotearo
Terms & ConditionsAccessibility statement