Populate portal rows with summary list values

[infoweb]

Hi there,

 

I'm a bit stuck with something and I'm hoping someone can shed some light on how to achieve what I'm after.

 

I have a database with products and purchase orders.

 

I have a global field that contains product id's in a list format.

 

I am trying to work out how to take that values from the global field list and populate the purchase order line items (which are in a portal on purchase orders) with the list values.

 

I need each list value to be inserted in a new portal row.

 

The number of values in the list would vary each time the action is performed depending on which products were pre-selected.

 

Can anyone help point me in the right direction?

 

Mark 

[comment]

There is only one way to "populate" portal rows: you must create records in the portal's source table. In your example, you would use a looping script to create a line item record for each value in the global field. However, the new records must also be related to a record in the parent (purchase orders) table. This part is missing from your description. Do you already have a purchase order to which these items should belong?

[infoweb]

Hi comment,

Thank you for taking the time to reply to my post.

If that's the case I can start the script by creating a purchase order then simply setting the purchase order id in a variable then begin creating records in the purchase order line items and setting the purchase order id match field.

I'm still not clear though how I would extract the product id's from my list into the product id match field for each record in line items.

I get the loop part that's fine, I'm just not sure how to set the product id field in line items with the product ids and then end the loop once the last id has been set.

Mark

[eos]

I'm still not clear though how I would extract the product id's from my list into the product id match field for each record in line items.

I get the loop part that's fine, I'm just not sure how to set the product id field in line items with the product ids and then end the loop once the last id has been set.

 

The basic idea is that within the loop you maintain an iteration counter that's used to a ) extract values from a list, using GetValue(), and b ) exit the loop once the list has been processed; so, assuming you have a list of IDs in a global field, and adding a bit of error-trapping …

If [ IsEmpty ( SomeTable::gListOfProductIDs ) ]
  Exit Script
End If
Set Variable [ $purchaseOrderID ; PurchaseOrders::PK_purchaseOrderID ]
Go to Layout [ LineItems ]
Loop
  Set Variable [ $i ; $i + 1 ]
  New Record/Request
  Set Field [ LineItems::FK_productID ; GetValue ( SomeTable::gListOfProductIDs ; $i ]
  Set Field [ LineItems::FK_purchaseOrderID ; $purchaseOrderID ]
  Exit Loop if [ $i = ValueCount ( SomeTable::gListOfProductIDs ) ]
End Loop
Set Field [ SomeTable::gListOfProductIDs ; "" ]
Go to Layout [ original layout ]

You could also create line items without leaving the PurchaseOrders layout if you use a portal to a LineItems relationship that allows creation of related records; it wouldn't require setting the PurchaseOrder foreign key, since that would already be part of the relationship definition.

 

On a related note: when populating the global field with product IDs, make sure you ignore / don't add IDs of products that already have a line item for the respective PO. You can use FilterValues to check on the presence of a value in a list, i.e. List ( LineItems::FK_purchaseOrderID ) ; this will save you from having to make that test when creating new line items with the above script.

 

EDIT: SomeTable::gListOfProductIDs in the Exit Loop

[comment]

I'm just not sure how to set the product id field in line items with the product ids and then end the loop once the last id has been set.

 

I was just about to answer this when eos posted his answer, so I'll just make a few mods:

Set Variable [ $purchaseOrderID ; PurchaseOrders::PK_purchaseOrderID ]
Set Variable [ $productIDs ; AnyTable::gListOfProductIDs ]
Go to Layout [ LineItems ]
Loop
    Set Variable [ $i ; $i + 1 ]
    Exit Loop If [ $i > ValueCount ( $productIDs ) ]
    New Record/Request
    Set Field [ LineItems::PurchaseOrderID ; $purchaseOrderID ]
    Set Field [ LineItems::ProductID ; GetValue ( $productIDs ; $i ]
End Loop
Set Field [ AnyTable::gListOfProductIDs ; "" ]
Go to Layout [ original layout ]

Note that we are assuming that the global field contains a return-separated list of the selected products' IDs - such as you would get if the field were formatted as a checkbox set..

 

 

 

  Exit Loop if [ $i = ValueCount ( $productList ) ]

 

I don't see that the $productList variable is defined anywhere.

[eos]

I don't see that the $productList variable is defined anywhere.

 

Right; I started out with that list variable, then noticed that the list is supposed to be stored in a global field, and forgot to change that bit. Good catch. 

[comment]

Right; I started out with that list variable, then noticed that the list is supposed to be stored in a global field

 

I have loaded it into a variable anyway, because I suspect it's a hair faster that way.

[infoweb]

Eos and comment,

Thank you for your suggestions.

I'll apply that today and come back with the results.

Mark

[infoweb]

I finally got around to implementing the suggested script and made a few tweaks so that it adds the line items directly through the portal.

 

Thanks again for the suggestion - a very clever yet simple and effective solution.