Mirroring records from one portal to another
I haven't yet cracked the problem from thread: http://forums.filemaker.com/posts/a8610a0c6f
Drugs----<Druglist_rx>-----Prescriptions-----<Input----<Druglist_in>---Drugs 2 (TO of Drugs)
Prescription, DrugList_rx, Input and DrugList_in are all shown in portals on a layout from a related table, 'L Patient Copy'.
I need the Drug IDs from Druglist_rx and Input ID from Input, to appear in DrugList_in every time a new Input record is generated. Obviously this is only to occur if there are Drugs listed in DrugList_rx. However, DrugList_in also needs to update should someone who has already generated an Input record along with a DrugList_in record, but then realises that their original 'parent' Prescription is incorrect and needs a Drug added or deleted. In such a case it's difficult to see how the amounts entered beside DrugList_in records won't lose synchronisation with the DrugList_rx list.
As an alternative I've thought of combining the 4 tables into 1 and accepting that Prescriptions and DrugList_rx have to be duplicated 1 for 1 with Input & Druglist_in records in order to maintain synchronisation. I was also thinking of reducing from 4 portals (1 for each original table) to 3: Prescription, DrugList_rx & _in combined, Input. If this method is OK I'd be interested to know how I can duplicate a record, but only certain field values and leave the rest blank.
I'd be interested in thoughts.