Possible option for a 2nd Relation?

For the 2nd time, I’ve come across a need for a 2nd or 3rd relation from a 2nd or 3rd field in the ‘current database’ to the same source db now so I’m wanting to put this out there as a wondering.

I am going to use ‘current database’ and 'source database as my terms as I know of no better term for the ‘current database’ than just that.

In one of my dbs, I have a Skipper field (which is a person), a Mate field (which is a person), and multiple CrewΩ fields which are people. Currently I can only have 1 relation for the db from either the Skipper field, the mate field, or the CrewΩ to the People file as each of those fields do relate a record in the People file.

This evening in a different issue, I have a Workorder db that has both a CompanyNum field and a TechNum field. In a TextDisplayObject, sometimes I want to pull the address from the Company file based on the CompanyNum in the Workorder db, and in another TextDisplayObject I want to pull the address from the Company db based on the TechNum.

Having an option for multiple relations for a ‘current db’ would solve these issues that might be another parameter in the Related( function.

When using the related( function, you can specify a different key field in the current database by using the “KEY” option, like this:

related({formula},"database","database name","key",{other key field})

You could also set this up in a dictionary, for example:

letpermanent alternateKeyRelation = initializedictionary(
    "database","database name","key","key field name")

Then you could use that anywhere like this:


Of course the variable could be named whatever you want, and you could have multiple different variables for different situations.

In fact you can do this in any code or formula based on a relation, including join, joinonerecord, related(, relatedarray(, findrelated, posttorelated, etc. That was quite a bit of extra work to set up and I wasn’t sure if that would be useful, but in retrospect that was a good idea :yum:

1 Like

This is a great feature and has been used in multiple situations already. I can see it being used by many. A baseball teams database with a Manager, Owner, CoachesΩ, TrainersΩ, and PlayersΩ fields, all needing to relate to the single People’s database. etc, etc.