Jump to content

Populate portal rows with summary list values


infoweb
 Share

This topic is 2507 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  :smile:

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

This topic is 2507 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.