LINKTOFORM Set Reference Column

Markus_Malessa · 12-16-2020 01:08 PM

The question at it’s simplest is whether LinkToForm can be used to set key pairs for a bridge table (reference table) within a form?

The scenario is I have a table ‘PermitList’ which has a column that stores keys to some self referenced rows, i.e. other permits that are linked with each other. When selecting a PermitList row I would like an action that opens up a form for my ‘Reports’ table and fills in the key pairs for the new report row and the relative permits into the bridge table.

To describe better what I want to accomplish. I have a permit with key 200, the column that stores all permit keys would for example say 100, 200, 300, and 400. When I click the action to start a new report, the form should ‘open’ and the ref column to the PermitList_Reports bridge table should be prefilled with the ‘New’ report key and each of the permit keys like NewReportKey-100, NewReportKey-200, etc.

I had initially tried doing this with LINKTOFORM(“FormName”, “VirtualColumnForReference”, LIST(Keys)) which does not seem to work. Is there some other way to get this accomplished in the form?

Steve

Yep.

You cannot set virtual column values except by the column’s App formula expression.

Markus_Malessa

So would LINKTOFORM(“FormName”, REF_ROWS(“Permits_Reports”, “Permit_fk”), PermitKeys) or something similar work instead? Or how might I get this accomplished? I know I can do the reference entries as a sequence of actions after the new report has been created, but really I would rather open the form with the reference entries already preset.

Steve

Please post a screenshot of the bridge table columns.

Markus_Malessa

Here is the bridge table

The cutoff column name is ‘MonthlyPermitDataPermits_fk’, which is the ‘Report’ table foreign key in the bridge table.

Steve

Like this?

LINKTOFORM(
  "FormName",
  "MonthlyPermitDataPermits_fk",
    ref-to-whatever1,
  "RelatedPermits_fk",
    ref-to-whatever2
)

Markus_Malessa

Hm, I have not tried that. I guess let me tinker around with that and see what the end result looks like. Still not sure how this might take care of ‘feeding’ the form a list of ‘PermitList_fk’ values and repeating the report key as the ‘MonthlyPermitDataPermits_fk’ value for each corresponding key in PermitList.

Markus_Malessa

Thanks for the help @Steve, but I think maybe I’m not explaining things very well of what I’m trying to accomplish.

So I start from PermitList Detail where I have a Virtual Column of Ref_Rows(“MonthlyPermitDataPermits_HAS_RelatedPermits”, “MonthlyPermitDataPermits_fk”). If I scroll down in the detail view I have that virtual column like so:

Clicking on the ‘Add’ button gets me to this:

I would then choose ‘New’ under ‘MonthlyPermitDataPermits_fk’ bringing me to yet another form like so:

You can see that following this process the bridge table entry is already filled in for the ‘single’ permit from which I originated from.
So lets say that my permit row has a virtual column that stores several keys (lets say 3 keys) from the permit table. My action button should link to the Report form, skipping over the intermediate bridge table form, but with the ‘Related Permits’ section already filled out, so essentially in this case there should be 3 entries in that section already.
2197C0b2 - PermitKey1
2197C0b2 - PermitKey2
2197C0b2 - PermitKey3

That is what I am hoping to accomplish. Unfortunately your formula suggestion did not accomplish this, unless I somehow need to nest a LINKTOFORM formula.
I can do this after the fact vs prefilling this section, by adding the references after I save the Report form, but that also becomes cumbersome because when using the ‘perform an action on a set of rows’ and then creating the reference table entries I cannot pass the key of the last created Report row rather I have to look it up by using MaxRow in the Report table. I have never liked that approach because of the possibility of Report entries getting created very close to the same time so I have always thought that this could possibly pull the wrong record. Maybe an unfounded concern.

Marc_Dillon

This is quite hard to decipher.

It sounds like maybe you’re wanting to auto-generate child records for a new parent record?

Markus_Malessa

More or less yes, however the child records in this case are not a one-to-many but rather the many-to-many in a bridge table.

So my report form has the ref rows as ‘is part of’ and that is the portion I would prefer being able to set via an action.