1 2 Previous Next 23 Replies Latest reply on Jul 12, 2012 11:55 PM by gullfounder

    Traversing through portal line item

    gullfounder

      Title

      Traversing through portal line item

      Post

      Hi,

         I am very new in File Maker Pro. Trying to learn it. But recently i have encountered a problem. I wanted to make very simple script. I have made a layout with portal in it and a button. A script is attached to the button. When all the required data has been putted.  Client will press the button to trigger the script.

      A script will simple traverse through the line items and update the inventory.

      Here is what i have written in the script.

       

      "Go to Portal Row [First]
      Loop
      Set Variable [$id; Value:Sale Detail::_fkproductID]
      Set Variable [$qty; Value:Sale Detail::Quantity]
      Exit Loop If[$id=User Prefrences::null]
      Perform Find [Specified Find Requests:Find Records; Criteria:Sale Detail::_fkproductID: “== $id”] [Restore]
      Set Field [Product::ProductInStock; Product::ProductInStock - $qty]
      Go to Layout[original layout]
      Go to Portal Row [Next]
      End Loop"

       

      Kindly help me fix it.

        • 1. Re: Traversing through portal line item
          GuyStevens

          Maybe you should first explain what it is you are trying to do.

          What table is your layout based on?
          What table is your portal based on?
          What is the relationship between the two?

          I don't understand the Exit loop step.

          My perform find steps are always split up in three (or four) steps.

          - Enter Find Mode
          - Set Field (Sets a specified field with a calculated result. The calculated result could ba a variable)
          - Set Error Capture (optional)
          - Perform Find

          Also, if you want to to inventory you could set up a table that contains:

          - A product ID.
          - An "Amount added"
          - An "Amount Sold"
          And fields that add up the Amount Sold and Amount Added and calculate the "Amount In Stock"

          That way, when you add a product to an invoice it get's sold and that means one item less in stock, and when you add stock, this count goes back up.

          This should happen in one table. This table can also be used as the "LineItems" table.

          And should be linked to the Invoice table but also the "AddStock" table.

          • 2. Re: Traversing through portal line item
            gullfounder

            I have 3 tables setup.

            Sale

            Sale Line

            Product.

            I made a layout in Sale Table. It contains the Portal to Sale Line. After user has added the Products in Sale Line using a Portal. There is button to update the inventory. On clicking it the script should fine each product in portal and its qty then update the product table. If there are no more item the script should stop.

            • 3. Re: Traversing through portal line item
              GuyStevens

              Is there a relationship like:

              Sale::ID------[=]------SaleLine::SaleIdFk

              Where Sale::ID is a number field with Auto enter Serial Number

              And SaleLine::SaleIdFk is a number field.

              ?

              And is there the same kind of relationship between

              SaleLine::ProductIdFk------[=]------Product::Id

              ?

              I would suggest something like:

              Set Variable [$SaleId ; Sale::Id]
              Go To Layout [SaleLine]
              Enter Find Mode [Uncheck Pause Box]
              Set Field [SaleLine::SaleIdFk ; $SaleId]
              Set Error Capture [On]
              Perform Find
              If (Get (FoundCount) *does not equal* 0)
                Go To Record / Request [First]
                Loop
                  Set Field [Product::ProductInStock ; Product::ProductInStock - SaleLine::Quantity ]
                  Go To Record / Request [Next ; Exit after last]
                End Loop
              End If
              Go To Layout [Original]


              This is just off the top of my head. I haven't tested this. But I think this shouldn't be to far off.

              But I do however nog like this system at all.

              What if you forget to update the stock? When of you accidentally update the stock twice?

              I would seriously consider using your structure you have now, but Adding some fields to the sale line so you can calculate the stock. 

              • 4. Re: Traversing through portal line item
                gullfounder

                Yes there Sale id is PK of Sale table. Sale id is FK in Sale Line table. The Product is table also have Product Id Which is also a foreign key.

                 

                I have made your script in File Maker. It changes to Find Mode and does not proceed further.

                • 5. Re: Traversing through portal line item
                  GuyStevens

                  Did you check the values in your fields?

                  Does the Sale::ID have a value?

                  Is that value also present in the SaleLine::SaleIdFk field?

                  And did you correctly set your variable?

                  The Product is table also have Product Id Which is also a foreign key.

                  The Product::ID is the Primary Key
                  The SaleLine::ProductIdFk would be the Foreign Key.

                  It changes to Find Mode and does not proceed further.

                  Did you uncheck the Pause box?

                  Does it say "Continue" in the top?

                  That means your script is paused. That's why you need to uncheck the Pause box on the Enter Find Mode script step.

                  • 6. Re: Traversing through portal line item
                    gullfounder

                    Yes Sale id have Value and it is also present in Sale Line table. My variable are correct. Product id is primary key in product table and is Foreign key in Sale Line table. yes it says continue in top.

                    I used the debuuger to check the script. It gave me error 400 at " if (Get (FoundCount ))"

                    i tried to put in the calculation as

                    "Get FoundCount "Not equal to sign " 0"

                    But it wouldn't allow me

                    Message that is given is "This paramert is invalid for Get Function."

                    • 7. Re: Traversing through portal line item
                      GuyStevens

                      yes it says continue in top

                      That happens because the Enter Find Mode script step has a Pause button. It is checked by definition. In my script I said:

                      Enter Find Mode [Uncheck Pause Box]

                      And in my previous post I said:

                      That means your script is paused. That's why you need to uncheck the Pause box on the Enter Find Mode script step.

                      So maybe you should try that.

                      For the Get (FoundCount) calculation you need to use the symbol for *Is not Equal to*

                      I can't type that symbol in here because it's a special character. But in Filemaker, in the calculation window you can add it from the top. It's a "=" sign with a "/" line trough it.

                      The calculation is supposed to check if the Found Count is not "0".

                      That's where you need this special character of *Is not equal to*.

                      Like so:

                      Does Not Equal

                      • 8. Re: Traversing through portal line item
                        gullfounder

                        I fixed the error 400 and pause issue is also solved. But during the debbug. I came across anothe error 101 at

                        "Go to record [Next; Exit after Last]"

                        I mean how is the record missing? I can see the next record after find mode.?

                        and another error is that

                        Set Field [[Product::ProductInStock; Product::ProductInStock - Sale Detail::Quantity]

                        it does not subtract the values in stock field?

                        • 9. Re: Traversing through portal line item
                          GuyStevens

                          It's possible that the Set field step doesn't properly do the calculation. In order to overcome that, do the calculation while setting a variable.

                          I Created a little test file myself. And I added a field in the SaleLine that get's checked when an item has been used to update the inventory. That way you don't update the inventory with the same sale twice:

                          The file:

                          http://dl.dropbox.com/u/18099008/Demo_Files/Inventory_StockUpdate.fp7

                          The Scrips:

                          Script

                          • 10. Re: Traversing through portal line item
                            philmodjunk

                            @DaSaint,

                            You do need the variable in your script example. Only data in variables or fields with global storage is accessible for setting up your search criteria once you enter find mode.

                            • 11. Re: Traversing through portal line item
                              GuyStevens

                              @PhilModJunk

                              But I'm no longer in Find mode when the calculation is happening.

                              The calculation being the first line in the loop. The Product::InStock - SaleLine::Quantity. As you can see in my last screenshot.

                              In my previous example (that I wrote off the top of my head) I had this calculation as the calculated value in a set field script step.

                              I did not even try to test this myself as Waleed assured me it didn't work, that's why, when I made my test file I immediatly put it in a variable and it worked.

                              Out of curiosity, I just went back to my test file and tried to see if I needed the set variable script step (the first step in my loop)
                              and it turns out I don't even need it.

                              I can just do my calculation as the calculated result in my set field step.

                              Thus making the actual script one step shorter:

                              Update Stock 2

                              • 12. Re: Traversing through portal line item
                                gullfounder

                                I have seen your script. It worked well. How ever in my file still got it error. Something is wrong in Product table or some where else. I have uploaded the file. Kinldy see it.

                                https://rapidshare.com/files/1364060131/Project_Filemaker TuckShop.fp7

                                • 13. Re: Traversing through portal line item
                                  GuyStevens

                                  I found your problem.

                                  There is no proper relationship between the SalesDetail table and the Products table.

                                  Because the dropdown used to select products has in it's valuelist settings the Barcode field as the first field and as teh second field... the barcode field again.

                                  This needs to be the PkId field as the first field so that the ProductIdFk field in the SalesDetail tables contains the actual ProductId. Not the Barcode.

                                  Also, if you want to try this multiple times, you always need to go and remove the "Yes" values from the Sales Detail field, or you need to temporarily remove that set field step in your find request. Otherwise the fields don't get updated again.

                                  Here's your file back:

                                  http://dl.dropbox.com/u/18099008/Demo_Files/Walleed%20Hassan/Project_Filemaker%20TuckShop.fp7

                                  • 14. Re: Traversing through portal line item
                                    gullfounder

                                    Thank you So much DaSaint. I really appreciate your help.

                                    Thanks Again.Laughing

                                    1 2 Previous Next