Subform in another subform: Handing over Foreign Key to a third table Topic is solved

[justme636]

Hello everybody!

I'm trying to create a customer database application with a central customer table and several subforms. I want a subform become available in another subform with information depending on the choosen customer. I think I can best explain with the following example of a rental car company: I have customers, cars and licenses. Each is a independent form/table. Then I have subforms of the customer table to assign licences and cars to each customer. Maybe here the following db structure plan helps:

db.png

So far everythings works. I can go to the table 'cust_car', open the form, pick a customer (foreign key from 'customer'), choose a car (foreign key from 'cars') and save this. Now I want from inside this form to show all the licences the customer has. It's really inconvenient to go back to the 'customer' table and check this manually. My plans looks like this:

workflow.png

I think the best way would be subform item, but it seems the subform items always use the table's primary key for the relation and I can't choose another key. In my case I would need 'cust_car_customer' from the 'customer' table as key and not the PK 'cust_car_id', which seems to be unchangeable for the subform object...

My workaround idea is to run the form 'cust_lic' in an iframe form inside 'cust_car' and use the field 'cust_car_customer' as filter. Also this works. The problem now is that when I click "add" in the iframe the form is empty and not yet conncted to the customer I chose in the beginning. Of course I could choose the customer now manually, but that means additional effort and possible mistakes - especially when there lots of customers with similar names. Apart form this rental car example, that is real problem in my real use case.

Does anybody know a way how to transport the choosen 'cust_car_customer' (foreign key) from the first subform and use it to prefill 'cust_lic_customer' when adding a new record? Or does anybody have an idea for another workaround?

I'm looking forward to your ideas and help

[treed]

I tried to do something similar and unfortunately that's not supported. See more discussion here viewtopic.php?p=15731&hilit=Place+subfo ... hin#p15731

[justme636]

Hello treed!

Thank you for your reply and sharing your experience! The discussion you linked to was a good starting point to get more information about my problem and I had to look at my problem in a new way: Actually, the Primary Key (from 'customer' in the example from above) and Foreign Key for all other table/subforms can be transmitted to another form. I found two options:

1) Retrieve as filter from form properties:

I created a run object in the first subform (the 'cust_car' table), picked the other subform (the 'cust_lic' table) as popup form and the variable of my foreign key ('cust_car_customer') as filter. In this popup (of 'cust_lic') the value the of foreign key is available as a form property, you can check Form Info > Current Properties the value of "filter" - it is the foreign key of the first subform that connecting all subforms to the third table ('customer') and used as the filter for the popup browse form. It was now a bit tricky to retrieve the form property as a value for a field in an edit form for a new record, but there is a way with

Code: Select all

nuFORM.breadcrumbs[nuFORM.breadcrumbs.length - 2].filter

as explained here: viewtopic.php?p=16792&hilit=nucurrentPr ... ter#p16792

2) sessionStorage

Also I found a second option to transport a field value to another form using sessionStorage. This is explained here:

viewtopic.php?p=23787&hilit=sessionStor ... tem#p23787

Maybe this helps somebody else with the same problem

Edit: I changed the subject to a more precise description of my problem and the solution.