you already have a portal displaying "related" medication on your consultation form. Assuming all the related medication records have the same date, or you have sorted the relationship appropriately, you should be able to use this:
set variable $id = Consult::__pkCOnsultID
If ( count ( Medication::_fkConsultID) > 0 ) <-- If there is more than one related record
set variable $date = Medication::DateStart
set variable $date = Get (CurrentDate) <-- Else, if there are no related records
go to layout - Medication
set field Medication::_fkConsultID -> $id
set field Medication::DateStart -> $date
Go to Object [Object Nate: "Medication Detail Portal"]
Go to Portal Row [Select; First]
Since none of your global fields are used in your relationship graph, you shouldn't need to use them at all. Use variables instead.
Thanks for this input. Unfortunately when I used the above I got nothing. The cursor just went to the last portal row. No records were added. I was also unsure what you meant when you said "Assuming all the related medication records have the same date or you have sorted the relationship appropriately...". The point is, medication for a specific consult (remember different clients will have multiple consults and there can be multiple medications per consult) have different dates.
When the "If (count..." is done above, the layout is still 'Consult'. Does Filemaker know to take the __pkConsultID, use it in the equation for _fkConsultID and look in the Medication table? I usually go to that table's layout first.
Let me know if you have any other thoughts.
Appreciate it. Mike
Yes, the count() function ( http://www.filemaker.com/12help/html/func_ref1.31.3.html ) counts records through a relationship. So you can count your related "Medication" records from a "Consult" layout.
Do you have FMP12 advanced? Using the script debugger and data viewer (tools menu), I would go through and see what is failing. I would imagine that something with the key handling is going wrong somewhere.
I noticed the above example I posted is missing a "go to layout - original layout" step after the two "set field" commands. I might also add a "refresh window - flush cached join results" to the end to refresh the data in the portal.