So basic data models is:
Parent Table 1 - Fee Model (1-Many)
> Child Table2 - Fee Model Options (related on fee model)
Parent Table 3 - Fee Proposal (lookup to select a fee model from table 1. One Fee Model can be used in many Fee Proposals)
> Child Table 4 - One Fee Proposal can have many Fee Proposal Options, dropdown of table 2* options. One Fee Model Option can be used in Many Fee Proposal Option records.
Table 3 (Fee Proposals) has a main form with Table 4 (Fee Proposal Options) as subgrid. When a new option is added it launches a Quick Create Form.
*The challenge - the lookup field in the the QCF needs to be restrict options to Table2 FM options where Fee Model is the same as Parent (Table 3)
I've tried a number of different things but each hit a dead end. I believe I need to either:
- Add Fee Model to Table 4 and somehow default the value to the Fee Model in the parent Table 3 (but not sure if this will allow the filter to work on a new record. OR
- Somehow pass the Fee Model ID and use it filter the Option look up in the QCF for Table 4
Either way I'm thinking this is probably a Javascript job, but new to this so any pointers welcome. Alternatively if I've missed any no code/low code solutions then please let me know.
Thanks in advance.
