Stamping child records

[Mike Walsh]

Is there any easy way, as there is in A4, to stamp field info from the parent record to a child record when entering a new child record other than just the linking field?

For example, a invoice is comprised of an "invoice_head" record and multiple "invoice_line" records where each "invoice_line" record needs to have the
invoice number (which is the linking field and can be stamped), the invoice date, the salesman ID, etc.

In A4 you could simply set the field rule to "calculated" and set the expression to the field in the parent record. How do you do this in A5 when you cannot get to the parent record when defining field rules?

Mike Walsh

[Selwyn Rabins]

RE: Stamping child records

you have to use the lookup() function in your field rule for the child table.

it can be a little tricky defining the lookup.

this should work, for example, to look up the "date" field in invoice header

lookup("invoice_header","invoice_number = '" +invoice_items->invoice_number+"'","date")

note: the above is equivalent to:

lookup("invoice_header","invoice_number = '000001'","date")

if the current record in invoice_items has an invoice number of 0000001.

to make the peformance of the lookup() function good, the field you are filtering on - in this case invoice_number in invoice_header table - should be indexed.

in a5v5 you will be able to directly refer to parent table fields in the field rule editor, and a5 will automatically construct the lookup() function for you - this should make old a4 users happy.

[Mike Walsh]

RE: Stamping child records

Thanks for the info. I have been using the "lookup" functions on the "order side" of this application and have been able to make it work, but for some reason it has been
more difficult in the invoicing portion.

The modifications that you mentioned in A5V5 certainly will help. Is there and estimate on when it will be available?

Thanks

Mike Walsh

[French]

RE: Stamping child records

What's wrong with simply adding the parent database (from which you want to "stamp" field values)into the set again as a child of the detail database using a one to one link. All fields from the parent should now be available to the child records through the parent appearing as its own grandchild. Or did I misunderstand what you want to do?

[Jim Rowlen]

RE: Stamping child records

Paul,
When I do what you ask it doesn't always work (whether ir not the parent has been saved yet). Instead, I DIM a series a variables in the script to use as buffers and use them. Sometimes these vars are used for a number of child files in certain larger apps. IE:
dim shared BfrSalesID as C
'etc
'create the invoice header
BfrSalesID = InvoiceHeader.SalesID
'etc
'end invoice header creation
'start creation of invoice line item
InvoiceItem.SalesID = BfrSalesID
'etc
'end creation of invoice line item

[Finian Lennon]

RE: Stamping child records

Mike:

This is one of those situations where I would probably break the "rules". I'd create a calculated field in the parent such as:

invoice_num+cdate(date)+Sales_ID

and make that the linking field. I would make sure that each component field is always fully populated i.e. if invoice_num is C,6 then I make it autoincrement from "000001" and if sales_ID is C, 3, I make sure that every entry is padded left "065" or "005".

Then in the child record I would break those values back out into the respective fields either as calculated fields or with a default value such as inv_num=substr(link_field,1,6)

I would do this in preference to multiple lookups.

Finian

[Mike Walsh]

RE: Stamping child records

I like your approach, I will give it a try

Mike Walsh