Reference Row Form inside Parent Form (One-2-One Relationship)

[Markus_Malessa]

I have a table of WaterUseData and MonthlyReports with a One-2-One relationship.
The MonthlyReports table contains an actual column of WaterUseData_fk referencing the ID of the related WaterUseData row.

I changed the reverse reference in the WaterUseData table to a LOOKUP() instead of the default REF_ROWS() formula so the virtual column in WaterUseData looks like this:

And the full formula is LOOKUP([_THISROW], "MonthlyPermitData", "WaterUseData_fk", "id")

I thought with this setup that I could show both forms together, meaning that when I open a form for MonthlyReports table it would show the form of WaterUseData inside the same form, but that is not the case.
How would I be able to show the one form nested inside the other form? Or would this require a sort of form navigation action sequence and if that is the case how might I be able to accomplish this? In my case, when I start the MonthlyReports form, it would require a corresponding WaterUseData entry along with it.

[MultiTech]

Hello @Markus_Malessa

I think you have your references backwards.

Markus_Malessa:

The MonthlyReports table contains an actual column of WaterUseData_fk referencing the ID of the related WaterUseData row.

Shouldn’t it be that the data records reference the report they belong to?

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

[Markus_Malessa]

Interesting that you should ask this question. So the relationship was originally created from inside Google App Maker. I do not recall which table I had selected when creating the relationship but I did set up a one-2-one and App Maker created the Foreign Key in the MonthlyReports table. Up until this point I never had any issues with this, so I guess I am uncertain how to answer your question.

I could probably write a script within App Maker to reverse this reference but ultimately my question would still stand if I could ‘house’ the reference form inside the other form without having to do the ‘dropdown’ and select new record in the reference, especially since I would like to force a new entry rather than being able to select one of the existing ones.

[MultiTech]

If you set things up as I had mentioned above (with the data entries containing a reference to the monthly report they belong to):

• If you turn on “Is a part of” for the data entries, then you will see (and have the ability to add) data entries inside the monthly report form.

This gives you a form inside a form;

• first you would open the monthly report;
• then, while still in that form, you would add data entries.
  • Clicking an add/new button, being taken to a new form (for the data entry);
  • after saving a data entry, you’re taken back to the report (where you can now see the entry you just made) - and you can repeat for however many entries you need.
• After you save the monthly report, all the records are saved at once.

[Markus_Malessa]

So the current set up is supposed to enforce the one-2-one relationship, which is why I used LOOKUP() instead of REF_ROWS(). So before I had it set up the way I think you are telling me. Here are some pics from current set up:

So in this case the WaterUseData_fk should be enforced as having to be a ‘new’ entry along with the MonthlyReports table entry. In this setup it does not appear to be able to be enforced because the dropdown allows me to choose an existing WaterUseData entry.

So I made the following changes and did the VC in WaterUseData as REF_ROWS() and instead of opening the MonthlyReport form I am opening the WaterUseData form instead.

So in this case it will only allow me to add one MonthlyReport entry to the WaterUseData entry, which is what I want, but I also would like the MonthlyReport form to appear within the WaterUseData form instead of having to click the ‘New’ button. I should probably also mention that the WaterUseData does not require a MonthlyReport entry in every case, because WaterUseData could be part of an AnnualReport instead in which case MonthlyReport reference would be blank. I can probably enforce that by using different forms, but do you mean I should actually assign the reference or the ‘ispartof’ in reverse to make it show up the way you are saying?

[MultiTech]

Markus_Malessa:

So in this case it will only allow me to add one MonthlyReport entry to the WaterUseData entry, which is what I want, but I also would like the MonthlyReport form to appear within the WaterUseData form instead of having to click the ‘New’ button.

There is no way of displaying a form inside of a form; you can navigate to a sub-form (when you press the new button), but you can’t display two at a time.

[Markus_Malessa]

Thank you for the clarification on this. Ultimately that is what I needed to know.

Alternatively, do you happen to know if I set up my form into page sections, would it be possible to automatically navigate to the sub-form as one of the pages in this case? Rather than having to click the ‘New’ button just make that sub-form the ‘Next’ page?

[Steve]

Markus_Malessa:

would it be possible to automatically navigate to the sub-form as one of the pages in this case?

Not possible.