Received an email from Don Wieland last night, with a completely different
approach - using a relationship to grab data from the previous portal row.
No scripts and no triggers involved....
I have tried to explain the technique below, but please email me if you
cannot follow and would like a copy of the solution.
Many thanks to Don for taking the time to suggest and explain this
technique. It's a great solution to my initial problem.
Regards
Peter
+ Parent record is a Voyage, with fields:
Voyage_id
Voyage_start (date)
Voyage_end (calculation, date) - via relationship to last portal record,
Departure date
+ Related Port calls are in a portal
+ Port calls each have a sequence number. Portal is sorted by sequence
number. First sequence must be 1.
+Portal rows have the following fields :
Sequence_No (number, first portal row must be 1)
Port_name
Arrival_date (calculation - see below)
Days_port (number)
Departure_date (calculation = Arrival_date + Days_port)
Days_sailing (number)
+ User enters a Voyage_start date on the parent record
+ User creates first portal record, with sequence set as 1
+ Arrival_date calculation is defined as :
Case
(
IsEmpty(Sequence_no), "",
Sequence_no = 1, Parent:Voyage_start, - in this first portal record, the
Voyage_start date is inserted
Via relationship to previous sequence number for this voyage:(Departure_date
+ Days_sailing) - the relationship finds port calls with the same matching
parent id but sequence number smaller than current record's sequence number,
sorts them into sequence number order, and uses record with highest sequence
number, ie the most recent port call record
)
+ User completes port days and sailing days
+ Create second portal record - sequence number anything above 1, say 2
+ The arrival date is grabbed from the immediately preceding portal record,
based on the 2nd Case statement in field definition for Arrival_date (ie the
relationship)
+ User completes port days and sailing days, and so on for as many ports as
required. Arrival and Departure dates are automatically calculated for each
new record.
+ Any Port_days or Sailing_days can be edited later - all subsequent portal
records are recalculated immediately. The Voyage_start date in the parent
record can also be changed, with portal records being immediately
recalculated.
+ Additional ports can be added sequentially, or inserted between existing
ports, eg with Sequence_no of 1.5 - again, all dates down the portal records
are recalculated, and because the portal is sorted on Sequence_no, the new
record snaps into the correct position between port 1 and 2
+ Voyage end date is the Departure_date from the last portal record
From: Don Wieland [mailto:[email protected]]
Sent: 03 March 2014 19:07
To: Peter Buchanan
Subject: Re: Exit portal trigger
Hi Peter,
Perhaps you should retying you data/relationship architecture (see attached
file below). I don't have time to post the specs of this to the FMP
LISTSERVER (super busy), but if it works for you, post your solution.
Good luck!
Don Wieland
D W D a t a C o n c e p t s
~~~~~~~~~~~~~~~~~~~~~~~~~
[email protected]
http://www.dwdataconcepts.com
Direct Line - (949) 336-4828
SKYPE - skypename = dwdata
