Loop script to update portal records

[docasar]

Hello,

I continue developing the POS and so far I have almost got everything to work as desired. However, there is still a functionality I will like to add. Right now I have a portal on the left which shows all the products pictures in grid, and when I click on the picture the script trigger adds the related item to the line_item table on the invoice.

So far the script looks like this:

Allow User Abort [ Off ]

#--------------Set Variable Product ID

Set Variable [ $PRD_ID ; Value PRODUCTS::ID_Product ]

#--------------Add new item to portal

Go to Field [ LINE_ITEM||id_invoice|::Product ]

Go to Portal Row [ Last ]

Set Field [ LINE_ITEM||id_invoice|::id_product ; $PRD_ID ]

Go to Field [ LINE_ITEM||id_invoice::Quantity ]

END

So what I would like to achieve now but I am not sure how, is to set the script so before it adds the related record to the line_item portal, it will check if the product has been already added and:

a) If it is not, then it should add it with quantity set to "1"

b) if it is already in, instead of adding it as a new row, it should add +1 unit to quantity.

Is this possible?

Thanks a lot for your help!

Luis.

 

 

 

Edited September 23, 2015 by docasar

[dwdata]

Allow User Abort [ Off ]

#--------------Set Variable Product ID

Set Variable [ $PRD_ID ; Value PRODUCTS::ID_Product ]

#--------------Add new item to portal

Go to Field [ LINE_ITEM||id_invoice|::Product ]

Go to Portal Row [ First ]
Loop
   Exit Loop If [IsEmpty( LINE_ITEM||id_invoice|::id_product) ]
   If[LINE_ITEM||id_invoice|::id_product <>  $PRD_ID)]
      Go to Portal Row [ Next, Exit After Last ]
   Else
      Set Field [ LINE_ITEM||id_invoice|::Quantity ; LINE_ITEM||id_invoice::Quantity + 1]
      Go to Field [ LINE_ITEM||id_invoice::Quantity ]
      Halt Script
   End If
End Loop

Set Field [ LINE_ITEM||id_invoice|::id_product ; $PRD_ID ]
Set Field [ LINE_ITEM||id_invoice|::Quantity ; 1]
Go to Field [ LINE_ITEM||id_invoice::Quantity ]

Edited September 23, 2015 by dwdata

[comment]

 

So what I would like to achieve now but I am not sure how, is to set the script so before it adds the related record to the line_item portal, it will check if the product has been already added and:

a) If it is not, then it should add it with quantity set to "1"

if it is already in, instead of adding it as a new row, it should add +1 unit to quantity

Try it along these lines:

Set Variable [ $PRD_ID ; Value: PRODUCTS::ID_Product ]
If [ IsEmpty ( FilterValues ( $PRD_ID ; List ( LINE_ITEMS::id_product ) ) ) ]
  # ADD A NEW LINE ITEM
  Go to Field [ LINE_ITEMS::id_product ]
  Go to Portal Row [ Last ]
  Set Field [ LINE_ITEMS::id_product ; $PRD_ID ]
  Set Field [ LINE_ITEMS::Quantity ; 1 ]
Else 
  # FIND THE EXISTING ROW
  Go to Field [ LINE_ITEMS::id_product ]
  Go to Portal Row [ First ]
  Loop
    Exit Loop If [ $PRD_ID = LINE_ITEMS::id_product ]
    Go to Portal Row [ next ]
  End Loop
  Set Field [ LINE_ITEMS::Quantity ; LINE_ITEMS::Quantity + 1 ]
End If

Caveat: I wrote this in a text editor and haven't tested it at all. You also need to replace LINE_ITEMS with the name of the TO used for the portal.

 

 

 

...

Go to Portal Row [ Last ]

Set Field [ LINE_ITEM||id_invoice|::id_product ; $PRD_ID ]

Set Field [ LINE_ITEM||id_invoice|::Quantity ; If(IsEmpty(LINE_ITEM||id_invoice::Quantity), 1, LINE_ITEM||id_invoice::Quantity + 1) ]

You have just now gone to the last portal row and created a new record. How can the Quantity field not be empty?

 

 

Edited September 23, 2015 by comment

[dwdata]

Comment - I thought the poster was auto entering a default QTY, at first. Code EDITED prior to you correcting me ;-)

[docasar]

Comment - I thought the poster was auto entering a default QTY, at first. Code EDITED prior to you correcting me ;-)

Allow User Abort [ Off ]

#--------------Set Variable Product ID

Set Variable [ $PRD_ID ; Value PRODUCTS::ID_Product ]

#--------------Add new item to portal

Go to Field [ LINE_ITEM||id_invoice|::Product ]

Go to Portal Row [ First ]
Loop
   Exit Loop If [IsEmpty( LINE_ITEM||id_invoice|::id_product) ]
   If[LINE_ITEM||id_invoice|::id_product <>  $PRD_ID)]
      Go to Portal Row [ Next, Exit After Last ]
   Else
      Set Field [ LINE_ITEM||id_invoice|::Quantity ; LINE_ITEM||id_invoice::Quantity + 1]
      Go to Field [ LINE_ITEM||id_invoice::Quantity ]
      Halt Script
   End If
End Loop

Set Field [ LINE_ITEM||id_invoice|::id_product ; $PRD_ID ]
Set Field [ LINE_ITEM||id_invoice|::Quantity ; 1]
Go to Field [ LINE_ITEM||id_invoice::Quantity ]

Hi Don,

Thanks a lot for the code  It almost worked, when I click on the products grid portal it adds +1 quantity (that is one part I wanted to achieve) however, if I click on different product, it just adds +1 quantity to the first product. 

In my original script when clicking on the product it would add the one related to the picture (the portal shows images on container) but now it would only add the first product of the product portal. Any further suggestion?

Thanks

Luis

 

Try it along these lines:

Set Variable [ $PRD_ID ; Value: PRODUCTS::ID_Product ]
If [ IsEmpty ( FilterValues ( $PRD_ID ; List ( LINE_ITEMS::id_product ) ) ) ]
  # ADD A NEW LINE ITEM
  Go to Field [ LINE_ITEMS::id_product ]
  Go to Portal Row [ Last ]
  Set Field [ LINE_ITEMS::id_product ; $PRD_ID ]
  Set Field [ LINE_ITEMS::Quantity ; 1 ]
Else 
  # FIND THE EXISTING ROW
  Go to Field [ LINE_ITEMS::id_product ]
  Go to Portal Row [ First ]
  Loop
    Exit Loop If [ $PRD_ID = LINE_ITEMS::id_product ]
    Go to Portal Row [ next ]
  End Loop
  Set Field [ LINE_ITEMS::Quantity ; LINE_ITEMS::Quantity + 1 ]
End If

Caveat: I wrote this in a text editor and haven't tested it at all. You also need to replace LINE_ITEMS with the name of the TO used for the portal.

 

 

 

You have just now gone to the last portal row and created a new record. How can the Quantity field not be empty?

 

 

Hi, many thanks for the code!! I am sorry but when I tried it didnt work.... it adds the right product to the line_item portal but fails to increase +1 quantity....

Any further suggestion would be much appreciated!

Thanks a lot!

Luis

[dwdata]

Sounds to me like your Product Button is not picking up the proper Product ID in your "products grid portal". Is the TO name of the portal for your "products grid portal named "Products"? If not, you are using the wrong name:

Set Variable [ $PRD_ID ; Value products grid portal name::ID_Product ]

If it is correct, then it is possible that the button is not align within the PORTAL ROW, thus it just picks up the first item in Products.

Just throwing out possibilities. Of course, if I could see the file, I can track down the issue immediately.

[comment]

 

I am sorry but when I tried it didnt work.... it adds the right product to the line_item portal but fails to increase +1 quantity....

Does this work for you?

 

PlusOneItem.fp7

[docasar]

Yes!! That is exactly what I needed! Thanks a lot!

[dwdata]

For educational purposes and to see things in action - I added my suggested script to Comment's demo. Both ways get the job done!

Glad you solved your issue ;-)

PlusOneItem2.fp7

[Agentshevy]

I am not sure if my topic clearly states my objective.. But this what l have in mind..  I have a simple invoice DB that,  items are added to the line item script... 

I did a search on the forum and I was able to get the first task sorted, which is automatically increase item quantity on the line item portal.. 

I did try to modify the script so as to work with Barcode scanner, but I wasn't successful.. 

Right now, my Barcode scans items without increasing their quality automatically..   I want to have a button (script) that can loop all through the line item portal, merging all identical products as one with just the quantity being increased..

This button can be clicked after scanning so as to tidy up the invoice..

[Agentshevy]

I have tried applying the two scripts on my solution it works perfectly... But when I tried using it for Barcode scanning..  I haven't been successful yet.. 

Right now, looks like a possible solution is to have a similar script run after barcode scanning process must have finished, such that it can loop through the line item portal and do the qty increment.. 

How possible is this?  Is it advisable? 

[Ocean West]

each scan adds a record with a set quantity of 1 if you scan multiple identical items you would have several individual records - your end goal is a single entry with the total count of matching items.

one option - don't scan the data in to the portal row. 

Scan the data into a global field that field helps establish a relationship (part of a multi predicate relationship) to the line items table - then a script trigger could create the record if one doesn't exist - then any scanned items that already exist would increment 1 to the sum of the related matching items.

Some barcode scanners can be programed to issue either a command-key or return or enter or tab key equivalent that could be handy to invoke a trigger on the field.

 

[bruceR]

No, it isn't possible to help if you don't accurately describe the problem.

In this case, you say you have a script that doesn't work.

And you hide the script. You don't tell anybody what it is.

What is the script you're using with the scanner? Better yet - a clone of the file.

[Agentshevy]

Thanks for your reply... But I must confess, I am totally lost 

[Agentshevy]

Ok..  I would upload the clone immediately 

[LaRetta]

It appears that you are jumping around trying to solve the same thing?  I had not seen this thread before or I would have simply pointed to it instead of creating another different demo.

They are not quite the same, since mine assumes the User is entering an identical LineItem and the prior quantity should be increased.  But it sure feels like you didn't achieve resolution and asking same question again is discouraged; best to stay on a thread you started for same problem. :-)

[LaRetta]

And here is a THIRD post about the same problems.

[Agentshevy]

Here is the file.. I needed to remove the product pictures because of the file size 

SimpleInvoice.fmp12

[bruceR]

There is no indication you have done anything to include the features specified by LaRetta in the other thread.

In particular, the relationship required to identify same-product.

As she suggests: go back to that thread and continue the topic there.

[LaRetta]

Except I still feel that we are playing Blindfold Pin the Tail on the TO.  And I am uncertain if it would work with scanner since I've never used scanner. 

All this bee-bopping around, compounded by too little information just doesn't work, ShevyShevy.  And I believe we've had these discussions before, haven't we?  Please just stay put on a thread until you achieve what you wish.  It won't hurt our feelings if we miss the mark - just be willing to provide more information as to WHY it didn't help you.

Everyone here can answer on a thread so you will never be left high and dry if we can help it.  :-)

[Agentshevy]

Ok.. Thanks for your replies... I need to locate that particular thread and continue from there 

Ok.. Thanks for your replies... I need to locate that particular thread and continue from there 

[Agentshevy]

Yea..  Really sorry about that... Not quite same problem but the problems are however related.. 

Some have been solved,  but it looks like implementing same technique on a script for scanning barcode hasn't been positive... 

Your replies and suggestions have been very helpful.. Thanks again 

[Lee Smith]

2 hours ago, shevyshevy said:

I need to locate that particular thread and continue from there 

Actually, you need to let me know which one(s) are in evolved, I will merger them now. You can do this be giving me the URLs to those 2, or is it 3 topics, see your Private Messages.

I was going to merge your topic yesterday, when LaRetta mention you had posted it for the third time, but unfortunately, the change in our site software, the threads are not easy to spot.

In the future, do NOT spread your questions about the same need over several threads.

Lee

[Lee Smith]

18 hours ago, LaRetta said:

And here is a THIRD post about the same problems.

I have contacted shevyshevy via PM.

[Agentshevy]

So sorry for that.. 

[Lee Smith]

Done

[Agentshevy]

Can I then stick with this thread.?

[Lee Smith]

That was the purpose of merging them.

[Agentshevy]

I have been able to modify my file, added one of Lareta's script on the quantity field (onobject exit) ..  Still haven't been successful 

SimpleInvoice_Mod.fmp12

[LaRetta]

Let's regroup ... 

In the thread you started with me, you didn't mention scanner - only that you were entering data into a portal and wanted to combine products as you data-entered.  There was more to my solution than the script - if you look in the graph, there was a self-join relationship in the LineItems table to isolate down to the 'original' LineItem product without leaving the current portal row.  Bruce even reminded you of the relationship but you didn't include it - did you look when he mentioned it?  Of course it would not work without that relationship. ;-)

But now that we know it is for scanner,  I would not recommend using a portal for data-entry (same advice given you by Ocean West - please read what he said again).  You can scan directly into a global field (as Ocean West suggested) and now let's turn back to Comment's file ( PlusOneItem.fp7 ) which I feel will provide you with what you need.  Consider this:

Instead of (or in addition to) the AllProducts portal for manual selection of ProductID (clicking the +), ProductID could be scanned into the global field and THAT is the ProductID that is passed on in Comment's script, adding a new product or increasing an existing product's quantity as needed.  I cannot assist with scanner behaviors and triggers but this should get you moving forward.

Hopefully those that have used scanners can fill in any holes if you get stuck with the scanner.  There are also threads about creating records using scanner - have you tried searching for them?

Edited November 9, 2015 by LaRetta

[Agentshevy]

When you say product ID can be scanned into global field,  I think in my file, that is what it's been implemented.. The g_search field is a global field, which holds the SKU number.. Or maybe I should try scanning the product ID instead of the SKU... 

As for threads for scanning records , I haven't seen one, but as you mentioned it, I think is the best place to begin with..  I will have to search for them 

 

Thanks for your reply.. 

[bruceR]

Scanning into a global field is fine.

Explain what should happen next.

Then write or modify the script to do that.

[Ocean West]

using a barcode scanner only facilitates the same thing of a super fast accurate typist - the solution should work if you hand type the value or if you use a barcode scanner to enter the data.  

[Agentshevy]

After several trials, I am happy to announce that I finally got the barcode script working.. Thanks to you all for your suggestions and especially the sample files, they were very helpful in studying how the scripts work.. 

I have uploaded the file for anybody with similar problem..  Thanks a million to you guys 

SimpleInvoice_Final.fmp12