10.2 Relational Management setup : source formula / Subsets with multiple matching keys

Panorama X Beta 10.2
1

Hi, I’m exited to be working with the new relational features of 10.2, and I’ve encountered a question I cannot solve via the built in help documentation. I’m trying to understand the workflow before I fully implement a database. Setting up the basic database relationships is VERY EASY in 10.2. I love the panel you have set up. The “click to make a key” and all that is great.

My basic concept is the following: multiple interrelated databases that share data related to a Key ID (an individual person), but each database holds a variable amount of data including a field that ranks the importance of the particular record. In human terms, this is data related to individuals (phone numbers, email addresses, etc, but also events such as phone calls and in-person encounters). Certain individuals will have more or less of one type of data stored about them, and I want to have one database that contains all the “most important” data from each auxiliary database. I prefer not to do this with Line Items because I would prefer just a single email or phone displayed in the primary database. I would like that to be whatever record in the auxiliary database is ranked as “most important.” I would like some sort of formula that looks like

"from Auxiliary database B, for all records matching ID XXX, select the “Email” corresponding to the lowest number value in field "Priority"

In tabular form what I have is something like the following

Table 1: People
Person_ID, Name, Notes, Some other Data, “most important email address”, “most important phone number”, etc…

Table 2 emails
Person_ID, nickname, Email address, Email Label, Priority

Table 3 Addresses
Person_ID, Street address, Label, Priority, City, State, etc

I have tried to find some way of expressing this in both the “subset options” and the “source formula” fields of the relationship management settings, but when I test it by adding 5 or 6 email address records, for a single Person_ID in the Email database, and give values of 1-5 for each entry, I then would like the “#1” priority listing to populate the related email field in the Persons table. I have understood that I need to set up triggers, and have tested the posttorelated() procedure. This results in one of the email addresses populating the target field, but not the one that corresponds to the correct value in the Priority column. I know I have some work cut out for me in understanding the best workflow in Panorama, but I would like to know if I’m chasing a wild goose, or if I’m just missing the obvious here.

2

I should mention that in my tests I structured the “Priority” field as integers with 1 being “top priority”. I also tested setting up “Priority” as a text field with words like “Primary” and “Secondary,” both entered manually, and as options to be selected, and tested it in the Subset options panel as «Priority» = "Primary" None of this seemed to work.

3

What you are trying to do is not possible with Panorama’s relational model, or I believe in any relational database. What you are describing does not correspond to the standard relational model. Panorama does allow multiple fields to be combed for use as a key, but it matches individual records. You can’t construct a “key” out of fields in different records, or do comparisons between different records.

So is what you are asking for impossible? No, I’m not saying that, but it will take additional coding on your part beyond the relational model built into Panorama.

If you are talking about a join operation involving the entire database, what you could do is sort the email and addresses tables by priority before doing the join. So for each person, the lowest priority data would be listed first (towards the top). Then you could specify that your join uses the first matching record, which would be the lowest priority record because of the sort.

If you are setting up a join just for the current record, you could manually do this in code. You would have to fetch all the matching records (the relatedarray( function could do this), then pick out the low priority one with your code. Or actually, I think I would use the arraybuild( function to gather the records, find the lowest priority one, then use the posttorelated statement to post that value.

One additional problem with your scheme is that there is nothing to prevent more than one email or address from having a priority of 1. With additional coding you could set up tiebreakers, for example most recently edited.

4

It doesn’t bother me specifically that more than one Priority could be set to “1” or even “Primary” (if I go with text). I was expecting to have to filter the data further or tell the database to select the top entry in the case of a tie, and any “idiot proofing” I have to do on my database will be different based on the specific structure of the data flow, so I didn’t want to spend too much though on that before learning what is possible via which tool.

I’m basically converting what I have woven together in a spreadsheet (where I pull matched data into a column from different sheets based on a sorted search or query with multiple key columns) to Panorama. The advantages of Panorama are numerous, and it’s worth my time to learn the specifics, but I didn’t want to beat my head against the Relationship Management form if I need to write a code with triggers to do what I want.
I had the sense that sorting the data either by formula or manually was not the most efficient way of pulling the information I wanted each time, and I played around with the arraybuild( function today after taking a break from the Relationship Management panel, and I think that may be the easiest to use for this specific task, from what I have seen.

There is so much capacity built into the Prodedures and functions in Panorama that it is daunting to know what tool is best for any given task. Thank you for the suggestion

5

I think I have come up with a solution that works, although it may not be the most elegant.

To rephrase the exact situation I’m trying to create:
I have databases related by some Key field (ID), and the child database has multiple records matching this ID. The normal operation flow is to choose “first”, “last”, or “ignore.” I wanted to select whatever record is ranked “most important” in some other field. This record may not be the first or the last depending on how the database is sorted.

In case anyone wants to do something similar, below is what I have done in the Database Options>Relations panel. For instance, I have a “Collector Profile” database set up where I store notes and information about specific collectors of my artwork. In this database I want a to display in what City is their “Main” residence. if I store all the addresses in one “Address Book” database, then for a particular individual, I can set a (subjective) Priority field value for each address, and set the following formula as the “Source Formula” in for the “Collector Profile” database “City” field:

arraylefttrim(arrayfirst(arraynumericsort(arraybuild(cr(),"Address Book",{Priority+"???"+City},{ID=fieldvalue("Collector Profiles",ID)}),cr()),cr()),1,"???")

This forms an array of all matching records for the id in the Address Book, with the Priority field value first, sorts the array by this value, selects the first entry in the array, then trims the Priority value out of the array to result in just the value for the most important record in the City field.

I think there may be a more elegant way to do this, but it’s a start. One issue remaining is that nothing yet stops me from having more than one “most important” value in the Priority field. However, this at least pulls the first one from among those ranked “most important.” Additionally, I know that there is a way to automate the fieldvalue( function using double chevrons, but it failed to function in every instance both in the formula workshop and in the Relations panel. Thirdly, this formula basically creates a 2D array manually, with the “???” being as a separator that is extremely unlikely to occur normally in a text string, but that is a possible point of failure.

With this in place, whenever I want to update a record, I can run a procedure like: posttorelated "Collector Profiles"

6

That’s a common technique. I think “???” is perfectly fine, but you could also use chr(1), an emoji (or emojis) or anything else.

Your bio says

Moderately comfortable writing/editing various scripts and code on the computer, but by no means “good” at it.

I think you are too modest, you’re quite good at it! Great job on this solution. :clap:

1 Like
7

Thanks! And the emoji or chr(1) idea is a good one. I think I’ll work that into my final formulae

8

In case someone follows me down this rabbit hole, I wrote a custom formula that I can use as the the Relations Management “source formula” for any number of fields. This allows you to define the key value either statically or by a formula like fieldvalue("",ID). Also the returnformula is very flexible and allows additional functions to be performed on the data (like getting a State name from an abbreviation) as in “City+’, '+state(State)” … any single word field name can be used in the formula without quotes, and custom text can be included with single quotes

let keyValue = parameter(1)
let searchDB = parameter(2)
let keysearchField = parameter(3)
let sortfield = parameter(4)
let RETURNFORMULA = parameter(5)

let arraybreak = chr(5)+¶
let sortbreak=chr(1)

local myValues

myValues = arraylefttrim(arrayfirst(arraynumericsort(arraybuild(arraybreak,searchDB,sortfield+{+sortbreak+}+RETURNFORMULA,keysearchField+{ = }+constantvalue(keyValue)),arraybreak),arraybreak),1,sortbreak)

functionvalue (myValues)