I have read a lot of discussion posts about topics that are very similar to this but there always seems to a knowledge gap or there is a practice being used that is advised against and a "for dummies" translation isn't provided.
I have a parent table (Deal) and a lookup table (KeyDatesLookupList) that I would like to insert the ID field into a line item table (KeyDates_Funding_Deal) to resolve the many to many issue. I have a screenshot below.
What is the best way to accomplish the following?
- Have all the unique record IDs from the (KeyDatesLookupList) added to another table when a new record is created. So, when a (Deal) record is created all the approx. 15 key dates are placed in the (KeyDates_Funding_Deal) table and the (Deal) record ID created is attached.
- When a new record is created in the Deal – Layout the ID field from the (KeyDatesLookupList) is added to the bottom of the (KeyDates_Funding_Deal) table and the new record number created is added in the fkDeal field.
- Have the ability to update my static lookup list of key dates (KeyDatesLookupList) if needed and have those new key dates be associated with deal records that aren’t assigned to that new lookup list item.
Other “Use” information
- I would like to have a portal of selected fields from the (KeyDates_Funding_Deal) table in my Deal-Layout I created. With the ability to go in for each Deal and enter the dates of key items for report filtering later.
- Attached are also the settings of the table relationships.
- I'm doing this all inside Filemaker no external date source. Although at some point I may upload some data but a very small amount.
- The plan is to use the AWS and FileMaker Cloud.
Please let me know if you have any questions. Depending on who you ask I’m fairly competent J and will be very responsive. I’m eager to solve this puzzle. Yes, I have watched a lot of video's and will hopefully/ eventually hiring a consultant to review my work but I need to get this project to a place that I can sell the FileMaker vision.