Creating related records - what way is simplest?

[willbown]

I think this is quite a generic task.

For example, we sell our Products in Packages. When we place a new Order, we want to create a list of Line Items for the invoice. There might be six Products in the chosen Package. We then want to create the corresponding six Line Item records.

In the past, I followed this approach:

1. Go to related records (show only related) [show the 6 Products in the selected Package]

2. Import from the related table [import 6 records from Products into Line Items, taking key data with you]

3. Replace key [Replace Order ID in Line Item records with the number of the current Order]

I wonder if there is a better way. Is there, for example, a web-safe way to do this, avoiding the import step? Is there a way to avoid the risk of records being locked during the replace?

Thanks in advance for any help.

[Reed]

What about something like having 3 portals on one layout. (Corresponding to the invoice records)

One portal would show the line items for a particular invoice

One would show a list of all products

One would show a detail list of the selected product

The product portal could use a type ahead filter to reduce the size of the shown list. When you click on one of the products, a script would set the appropriate global field for the detail portal to show all line items relating to that product. Finally, your "add to invoice" button would be attached to a looping script that steps through each of the rows of the detail portal, puts each product ID in a global field, and then uses the global to do a set field for the id field in the last row of the invoice line items field. You have to allow creation of related records in the invoice line items portal....

Let me know if you need a more detailed description.

Dana

[Fitch]

There are many ways to approach this. You could GTRR products, but then instead of import, loop through the product records. For each one, run a subscript in Line Items that creates a new record with that product info. Instead of using Replace, store the Order ID in a global before you go to Products, or in a Script Parameter, and set it into the Line Item records as they are created.

[Ugo DI LUCA]

Lots of ways...

- 1. store the list of Items IDs in a ValueListItems calc and include it as a Script Parameter

- 2. set the current PackID in a global field

- 3. loop, creating new records in Line Items (with PackID field autoentering the gPackID)

- 4. parsing the ItemID from the Get(ScriptParameter) by using Count(Order and ID selfRelationship::serial)

Or a new way I'm now using (when the list of related records might be long) is to transfer the ValueListItems in a repeating calculation, and then import it back from the LineItems. IMO, that's the quikiest way if you know in advance what could be the largest number of related records.

[willbown]

Thank you all for your helpful replies. Of the three suggestions, I think the one from Fitch is the one I will use. It most obviously reflects the form of the problem and the database. However, if it was not for the web issue, I think I would stick with my old method. An import seems to me more straightforward (and less vulnerable) than the loops we're discussing here.

[T-Square]

To elaborate on Fitch's suggestion, if you are ALWAYS creating the Line Item records in this way (i.e. from an Order), then I would actually modify the Line Item table so that the OrderID is filled in from the global field that you set in your script. That way, all you have to do is create the record, and it will automatically get the OrderID from the global. Obviously, you have to be careful that your global is correctly set.

[Ugo DI LUCA]

And to elaborate on mine....

Create a calculation

cProductsInPackage (repeating calc set to your max number in package)

Let([

R = Get(CalculationRepetitionNumber);

VL = ValueListItems(Get(FileName);"RelatedProductIDs")];

MiddleWords(VL;R;1))

What you need is create a related value list based on the PackageID stored in your Global Field (if you opted for a global)

Then in Order, you have OrderID, gPackageID and this repeating calculation.

Therefore, you may use an import script now that will split the content of the repeating calc field into multiple records, with PackageID autoentering gPackageID and OrderID autoentering OrderID.

Of course, prior to this script in LineItems, you'd have limited the foundset in Orders to just the new order you are creating.

Hope this makes sense. I just found this new 7 feature interresting to point.