adding records that fill in two fields automatically

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a list of firms in a parent table. For each firm, I need to track payrolls both by date and by number in a child table with some additional information.  Would it be possible to auto populate the date and number fields each time you add a record and select the contractor?  The payroll #s would go 1, 2, 3... the dates would be populated based on their start date and continue every 7 days. 

Thank you

Photo of Alison


  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
You have to use script to do this and it is easy to implement. Here is a sketch of what you have to do:

  1. implement the image onload technique on the child table to respond to ?a=API_GenAddRecordForm
  2.  grab the &_fid_7=1 parameter off the URL. This parameter will be automatically inserted if you click on the add Child button on the parent
  3. in script query the existing child records related to the parent record and extract the values for the date and number
  4. bump up the date by one week and increment the number by one and stuff these values into the appropriate field on the  ?a=API_GenAddRecordForm page
Again this is easy to do but if you want a custom solution please feel free to contact me off world using the information in my profile.
Alas, when Dan offers up non native solution, I feel compelled to reply with a native solution.  In your case I suggest a KISS approach.

You probably have a table of Contractors which has an embedded report of Payrolls.  I suggest that you do a summary field of the maximum of the payroll #.  Also a field for the maximum of the Payroll Date.

Then there is an existing button to Add Payroll,  If you post that code and let me know the field ID numbers of the two fields to be updated I can help you.  for example

URLRoot() & "db/" & [_DBID_PAYROLLS] & "?a=API_GenAddRecordForm&_fid_10=" & URLEncode ([Related Contractor])

& "&_fid_99=" & ToText([Maximum Payroll #] +1)

& "&_fid_100=" & ToText([Maximum Payroll date] + Days(7))

& "&z=" & Rurl()
In the example above the field ID's for the two fields to be populated are 99 and 100
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Forsooth, when Mark offers up a native solution to "one up" my non native solution I feel compelled to reply with a new technique that solves whole classes of problems.

It would not be difficult to supply a generic function which would calculate various child field values whenever a new child record is created. So if you want to index your child records with roman numerals, initialize dates according to some scheduling scheme or otherwise set field values in the child record to implement some custom logic all you would have to do is supply a short JavaScript function.

I am a bit behind schedule at the moment but I will put this on my "techniques to implement" list.

Just pulling your chains Mark. If you understand Mark's methods you will see that there is often a high degree of duality between native and script solutions.