Not too hard if all of the purchase orders will be added by clicking the "
Add Purchase Order" button in the Jobs table.
If that is the case then you can achieve this in 3 steps:
- Create a summary field in the jobs table to count the number of purchase orders called "# of Purchase Orders"
- Create a formula text field in the jobs table called "Next Purchase Order Number"
- Modify the "Add Purchase Order" formula URL field to write the "Next Purchase Order Number" to the purchase order number field in the Purchase Orders table
Formula for the "Next Purchase Order Number" fieldvar text jobNumber = ToText([Job Number]);
var number numberOfFollowingZeros = 4-Length(ToText([# of Purchase orders]));
var text followingZeros =
Case
(
$numberOfFollowingZeros
,3,"000"
,2,"00"
,1,"0"
,0,""
);
var text finalNumber = ToText([# of Purchase orders]+1);
$jobNumber & $followingZeros & $finalNumber
Sample formula for the "Add Purchase Order" field. (I was writing to field number 8 in the purchase order table)URLRoot() & "db/" & [_DBID_PURCHASE_ORDERS] & "?a=API_GenAddRecordForm&_fid_7=" & URLEncode ([Record ID#])& "&_fid_8=" & URLEncode ([Next Purchase Order Number]) &"&z=" & Rurl()
