Using related( with LineItemΩ fields in a Text Display object

Panorama X
1

I believe that I should be able to pull in data from a Source db into the current db directly in a Text Display Object. Maybe I’m wrong but I can’t seem to figure out the proper syntax.

My Current db has 4 fields Code1, Code 2, Description1, & Description 2.

My Source db has 2 fields, ‘Code’ and ‘Description’.

My relation has CodeΩ in the Current db & the ‘Code’ field in the Source db as the Key Fields. The relation also shows the DescriptionΩ field is linked to ‘Description’.

If I have Auto Join enabled, when I enter a code into Code1, the proper description is entered into Description1.

I believe that I should be able to use the related( function in a Text Dispay box similar to the way I might use a Lookup but with a Relation already set up, I should be able to use the related( function instead. If I do the Auto Join and the Description is then sitting in that Description1 field, I could directly access that data but the reality is that I have 40 codes and the Descriptions are quite long, with over 80,000 records so I would like to just do the related( in the Text Display object without actually pulling any data into the db if it is not necessary.

My current formula in the Text Display object is…

related({Code1},”7-11 Echo Codes”)

The current error in the Text Display object is

“Field or Variable [Code1] does not exist.

What would the formula be in the Text Display object to display what could otherwise be Auto Joined into Description1?

2

What you want to do is not possible with the related( function.

The first parameter of the function is the field in the source database that you want to extract data from. So that needs to be {Code}, not {Code1}.

The related( function can be used when there is a one-to-one relationship between the current database and another database. But in this case, there isn’t - instead there is a one-to-one relationship between each line item and the second database. The related( function has no way to specify which line item you are interested in. So it won’t work for you. This isn’t mentioned in the documentation because I never thought of it, and apparently you are the first person to think of trying this.

Theoretically there could be a relatedlineitem( function (or some similar name) that would have an additional parameter to specify the line item somehow. For now, however, you’ll simply have to use a lookup( function.

Note that there is no performance difference between using the lookup( function and the related( function, the related( function is simply more convenient to use because you don’t have to specify the relationship in each formula. Where you get a huge performance boost is in using join instead of formulafill lookup(…), but for one off items in a text display object, there is no performance advantage to using the related( function.

1 Like