Using the Related( function with a data formula generated "on-the-fly"

Panorama X
1

The following function incorrectly returns information from the currently selected record in the related database, instead of the related record information:

related({datavalue("FieldA"+"")},"RelatedDatabase")

I thought the above should be equivalent to:

related({FieldA},"RelatedDatabase")

which works as expected.

2

I think both formulas are doing exactly what I would expect.

The related( function finds the related record, then evaluates the formula with that record as the context for any field values. But datavalue( has it’s own logic for determining what the current context is, so it ignores the context supplied by the related( function.

What are you really trying to do? Do you want the field name to be the result of a calculation? Then you could do something like this.

let targetField = "FieldA"
if weird
    targetField = "FieldWierd"
endif
let data = related(targetField,"RelatedDatabase")

Or for this example you could just do this:

related(?(weird,"FieldWierd","FieldA"),"RelatedDatabase")

The documentation says that the first parameter of the related( function must be quoted, but that is really a simplification. The actual rule is that this parameter must itself evaluate to be the formula you actually want to use. Usually, you just put quotes around the formula you want, but you can actually assemble the formula “on-the-fly” if needed.

3

Yes, it is something like this:

fileglobal fgVar  // it can be "Mail", "Bill", or "Ship"
related({
datavalue("Address1"+fgVar)+¶+
datavalue("Address2"+fgVar)+¶+
datavalue("City"+fgVar)+¶+
datavalue("State"+fgVar)+¶+
datavalue("Zip"+fgVar)
},"Related Database"))

I have 3 different addresses stored in the related database (Mail to, Ship to and Bill to), and the variable determines which one to get back.

4

I guess I wasn’t clear, sorry. What you need to do is something like this:

related({Address1}+fgVar+
    {+¶+Address2}+fgVar+
    {+¶+City}+fgVar+
    {+¶+State}+fgVar+
    {+¶+Zip}+fgVar,"Related Database")

What this will do is first evaluate to a formula with your actual field names. Then this formula is passed to the related( function. Does that make sense?

None of your fields require chevrons, but if they did you could do it like this:

related({«Address1}+fgVar+
    {»+¶+«Address2}+fgVar+
    {»+¶+«City}+fgVar+
    {»+¶+«State}+fgVar+
    {»+¶+«Zip}+fgVar+{»},"Related Database")
5

Thank You Jim,
I now understand how the formula works inside a related function.

6

FYI, these techniques will work anywhere that the documentation says “the formula must be quoted.”