Help

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

601 1
cancel
Showing results for 
Search instead for 
Did you mean: 
RockyCCChong
6 - Interface Innovator
6 - Interface Innovator

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 2

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 Ng
Programme Coordinator at Sistema Aotearoa
https://www.sistemaaotearoa.org.nz/

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.