1 2 Previous Next 16 Replies Latest reply on Jan 14, 2011 9:30 AM by philmodjunk

    Change Lending Library starter solution Process Check Out script to accommodate a Quantity field

    JamesClark

      Title

      Change Lending Library starter solution Process Check Out script to accommodate a Quantity field

      Post

      In the ProcessCheckOut script, in the History section of the Scripts in the Lending Library starter solution I'm trying to adjust it so that when someone presses the 'check out' button on a record in the Assets layout, the script performed will only change the AssetId status to 'not available' if the value of the field 'Quantity' is less than or equal to 0. I added the quantity field to the Assets and History table and modified the script so that upon executing the ProcessCheckOut script, when the other values already in the script are committed to a new history record, so too would the value of the Quantity field I created in the History::Quantity field. 

      As I understood it and how I intended this to work was that a user specifies the quantity of an item they want to borrow from the Item's record which updates the value of the quantity field. This value is then committed to the history::quantity field from the assets:quantity field and the part of the ProcessCheckOut script which originally said set field [Assets AssetId::status; "Not Available"] is now part of an if condition which evaluates whether or not the Assets::Quantity field is less than or equal to 0. If it is, then the asset is not available otherwise it should remain available.

      the script appears to still execute just fine, but it's just ignoring my changes, an item is considered checked out if the check out button is pressed in the Assets layout and the quantity of that item available appears to have no bearing on this, if I check out 2 of an item of which there is a quantity of 5 leaving 3 remaining for lending: attempting to borrow any of the remaining 3 simply results in the message "this item is already checked out" appearing. I don't know where I've gone wrong.

      If it's not immediately apparent, I'm not a programmer and I'm just trying to follow the logic of the scripting as best I can. Please forgive any grievous errors I've apparently made, it comes from my naivety.

        • 1. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
          philmodjunk

          So you've added a Quantity field to both the History and the Assets table. Presumably History::Quantity would record the Quantity checked in or out and Assets::Quantity records the amount currently on hand?

          Do the check in and check out scripts use the value intered into History::Quantity to update the amount in Assets::Quantity by adding or subtracting that value from the current number in Assets::Quantity?

          • 2. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
            JamesClark

            Well actually there's another field that I have only in the Assets table which is QuantityBeingBorrowed which is used to update Quantity in Assets. History Quantity is supposed to reflect whatever was in the Assets::Quantity field after the user has selected a quantity to borrow and hit check out. Based on what I've understood the logic to be by looking at the scripts and relationships, the history table is used to create a record of the details of an item being borrowed and who is borrowing it. Thusly my history::quantity field is set to the value of the assets::quantity field at the same time as the assets::assetID field is set to the history::assetID field. However looking at the script there is some complicated arrangement with fields named AssetID1 and extra tables with Assets in their names but as far as I could work out this wouldn't affect what I'm trying to do which is simply to allow more than one instance of borrowing to occur on any item where the assets::quantity field is more than 0. 

            As I type this I realise I've made one silly mistake which is that if I commit the value of the assets::quantity field to the history::quantity field then the amount remaining not the amount the user borrowed will be what is recorded however despite this I still don't understand why my if calculation for evaluating whether or not an item is available based on the assets::quantity field doesn't appear to work. I'll have to fix the section that records the quantity borrowed in the history table so that it links to the QuantityBeingBorrowed field but otherwise I'm still facing the same dilemma.

            • 3. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
              philmodjunk

              You might want to post your script. If you have FileMaker Advanced, you can copy and paste scripts from a Database Design Report. With Regular FileMaker, you can use Script Manager to Print the script to a PDF and then open the PDF and use the PDF reader's text tool to copy and paste the text. You can also upload a screen shot of your script to the first post you made after clicking the edit link for it or you can upload the image to file sharing site and then post the link to it here.

              • 4. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                JamesClark

                Okay, thanks so much I'll get started on that. Hopefully you'll be able to tell what is my addition to the script but if you can't I'll be able to point it out to you.

                • 5. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                  JamesClark

                  Allow User Abort [ Off ]

                  Go to Layout [ “Process Check Out” (History) ]

                  Show/Hide Status Area

                  [ Lock; Hide ]

                  Adjust Window

                  [ Resize to Fit ]

                  New Record/Request Set Field [ History::Asset ID; Assets One::Global Asset ID ]

                  Set Field [ History::Contact ID; Contacts One::Global Contact ID ]

                  Set Field [ History::Quantity; Assets::Quantity ]

                  Set Field [ History::Check Out Date; Get(CurrentDate) ]

                  Set Field [ History::Check Out Time; Get(CurrentTime) ]

                  Commit Records/Requests Loop

                  Pause/Resume Script [ Indefinitely ] If [ IsEmpty(History::Due Date) ]

                  Beep

                  #

                  Show Custom Dialog [ Title: "Message"; Message: "You must enter the due date."; Buttons: “OK” ] Else If [ GetAsNumber(History::Due Date) < GetAsNumber(History::Check Out Date) ]

                  Beep

                  #

                  Show Custom Dialog [ Title: "Message"; Message: "The due date cannot be before the check out date."; Buttons: “OK” ] Else If [ GetAsNumber(History::Reminder Date) > GetAsNumber(History::Due Date) ]

                  Beep

                  #

                  Show Custom Dialog [ Title: "Message"; Message: "The reminder date cannot be after the due date."; Buttons: “OK” ]

                  Else

                  Exit Loop If [ 1 ] End If

                  End Loop

                  #

                  If [ Assets::Quantity ≤ Int ( 0 ) ] Set Field [ Assets Asset ID::Status; "Not Available" ]

                  End If

                  Go to Layout [ “Asset Form View” (Assets) ]

                  Show/Hide Status Area

                  [ Show ]

                  Adjust Window

                  [ Resize to Fit ] Move/Resize Window [ Current Window; Width: 909 ]

                  • 6. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                    philmodjunk

                    Took the liberty of using a custom function I wrote to clean that up a bit so that it's easier to read:

                    Allow User Abort [ Off ]
                    Go to Layout [ “Process Check Out” (History) ]
                    Show/Hide Status Area [ Lock; Hide ]
                    Adjust Window [ Resize to Fit ]
                    New Record/Request
                    Set Field [ History::Asset ID; Assets One::Global Asset ID ]
                    Set Field [ History::Contact ID; Contacts One::Global Contact ID ]
                    Set Field [ History::Quantity; Assets::Quantity ]
                    Set Field [ History::Check Out Date; Get(CurrentDate) ]
                    Set Field [ History::Check Out Time; Get(CurrentTime) ]
                    Commit Records/Requests
                    Loop
                           Pause/Resume Script [ Indefinitely ]
                           If [ IsEmpty(History::Due Date) ]
                                   Beep
                                   #
                                   Show Custom Dialog [ Title: "Message"; Message: "You must enter the due date."; Buttons: “OK” ] 
                            Else If [ GetAsNumber(History::Due Date) < GetAsNumber(History::Check Out Date) ]
                                   Beep
                                   #
                                   Show Custom Dialog [ Title: "Message"; Message: "The due date cannot be before the check out date."; Buttons: “OK” ]
                           Else If [ GetAsNumber(History::Reminder Date) > GetAsNumber(History::Due Date) ]
                                   Beep
                                   #
                                   Show Custom Dialog [ Title: "Message"; Message: "The reminder date cannot be after the due date."; Buttons: “OK” ]
                           Else
                                   Exit Loop If [ 1 ]
                           End If
                    End Loop
                    #
                    If [ Assets::Quantity ≤ Int ( 0 ) ]
                           Set Field [ Assets Asset ID::Status; "Not Available" ]
                    End If
                    Go to Layout [ “Asset Form View” (Assets) ]
                    Show/Hide Status Area [ Show ]
                    Adjust Window[ Resize to Fit ]
                    Move/Resize Window [ Current Window; Width: 909 ]

                    Earlier, you said: " there's another field that I have only in the Assets table which is QuantityBeingBorrowed which is used to update Quantity in Assets."

                    I don't see that field being used to update Assets::Quantity.

                    • 7. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                      JamesClark

                      Sorry if I'm being too vague, I was trying not to have to flood too much information in to the post in case no one wanted to answer it and I thought it wouldn't have a bearing on the specific things I'm trying to troubleshoot. Okay so the QuantityBeingBorrowed field is in the Assets layout and the assets table, in the layout there is a field labelled 'How many?' with two buttons plus or minus, plus a confirm button. I have scripts for each button which adjust the value of QuantityBeingBorrowed plus or minus 1 and hitting confirm updates the Assets::Quantity field which is being referred to in the script I posted to you before.

                      • 8. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                        JamesClark

                        My hope in doing things this was that I could keep my changes to the original script to a minimum so I could more easily track where things might go wrong because I'm not so confident in my programming ability. I was hoping that the less new fields and variables involved with ProcessCheckout script the better so I might better understand what I was doing. I have to leave the office now though, I would still appreciate your help if there's anything you think you can do. I'll be back in tomorrow, and would love to continue troubleshooting if you need more information from me and can instruct me.

                        • 9. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                          philmodjunk

                          Yes, but you still need to take the quantity being borrowed and subtract it from Assets::Quantity or this value will never change and thus you will never see an asset become unavailable due to all its items being checked out. I'm just trying to point out that you need one more script step here to update this field.

                          • 10. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                            FentonJones

                            If you have buttons on the layout, to see the Asset quantity field directly, then why would you even allow the script to run if the quantity is insufficient? It seems the test should be near the top of the script, before going anywhere or creating a History record.

                            The Asset Status field could be an auto-enter, via calculation, with [ ] Do not replace contents unchecked. Or it could be just a calculation, z_cAvailable, with a Boolean number result (since something is either available or not). Booleans are just easier to use for things (like a relationship to filter a portal or value list to only available items).

                            Case ( Quantity ≤ 0; "Unavailable, "Available" )
                            or, Boolean calculation: Quantity > 0

                            One other niggling thing about the script. You're setting the History::AssetID to the global AssetID (which we don't see where it gets its value, but we'll assume somewhere). Then you're using it immediately in a relationship, to get a value from Assets. From my understanding, best practice is to Commit Records before using a field in a relationship if it has just been set. It seems superfluous, but makes it reliable. Maybe I'm just old-fashioned.

                            One more niggle. It seems to me that Due Date should have a default value, or an input field in a dialog, or something. It seems rude to Beep and tell them there's no Due Date when they've not had a chance (that i can see) to enter one.

                            Also, there is no Cancel. Which is OK I guess. And perhaps you want a Loop they can't get out of without entering a valid due date. But a Pause [ Indefinitely ] at the beginning is wrong. A Custom Dialog is automatically a infinite Pause. Also, it would be nice give the dialogs real titles, "Message" is kind of lame.

                            • 11. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                              JamesClark

                              QuantityBeingBorrowed is subtracted from the quantity after the confirm button is clicked.

                              Frenton, I'm somewhat nervous about changing very much of the script because it's all part of the starter solution except for some very minor changes from me. I don't want to break anything by fiddling around too much so best practice might be a good idea most of the time but I'd like to avoid changing stuff around unless absolutely necessary. However preventing the script from executing when quantity is set to zero at the top of the script sounds like a good idea. My problem now though is making an item still able to be checked out if the quantity is above zero but a few of that item have been loaned out already.

                              • 12. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                                philmodjunk

                                QuantityBeingBorrowed is subtracted from the quantity after the confirm button is clicked.

                                Not in the script that you've posted here. What confirm button is this and how does it connect to the script you've posted?

                                • 13. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                                  JamesClark

                                  Apologies. I have scripts for each button which adjust the value of QuantityBeingBorrowed plus or minus 1 and hitting confirm updates the Assets::Quantity field which is being referred to in the script I've posted. 

                                  In a strange further development, you can see here that I have this step in the script

                                  Set Field [ History::Quantity; Assets::Quantity ]

                                  which I added myself to accommodate my addition of the idea of a quantity of an item in this database, my hope being that the history record which would log the details of this particular instance of borrowing would now also log how many of an item was borrowed in addition to the other fields committed to the new history record upon executing the ProcessCheckOut script. I realised however that I should be recording the QuantityBeingBorrowed not the quantity remaining of an item (Quantity) and so have now changed the relationship and the script to reflect this so that now it is

                                  Set Field [ History::Quantity; Assets::QuantityBeingBorrowed ]

                                  I've also added to a separate script (another written for the starter solution) - the ProcessCheckIn 

                                  Set Field [ Assets::Quantity; Assets::Quantity + History::Quantity ] 

                                  My intention here being that when an item is returned, the script will add how many were borrowed back to the quantity field in the asset's record thus reflecting the true number of items remaining.

                                  While the original problem I've posted about is still happening, that is if you check an item out, it doesn't matter how many of that item is checked out, the database considers to be unavailable even when there are more items remaining. Now there is another strange problem

                                  Despite a relationship being defined between Assets::QuantityBeingBorrowed and History::Quantity and a script step being added to the check in script to facilitate checking in a specific number of items that reflects how many were borrowed to begin with, the quantity field remains unchanged, I added a box to the asset layout to display the value of history::quantity so that I could see what was going on and there is no value here at all. Somehow the value of Assets::QuantityBeingBorrowed is not being sent to History::Quantity. I'm not entirely clear why.

                                  If it helps to avoid more confusion here are the 3 scripts I wrote for each of the buttons affecting QuantityBeingBorrowed, the Plus, Minus and Confirm buttons on the layout.

                                  AddOneToQuantityBeingBorrowed

                                  If [ Assets::Quantity > Int ( 0 ) ]

                                  If [ Assets::QuantityBeingBorrowed < Assets::Quantity ]

                                  Insert Calculated Result [ Assets::QuantityBeingBorrowed; Assets::QuantityBeingBorrowed + Int ( 1 ) ] [ Select ]

                                  End If

                                  End If

                                  SubtractOneFromQuantityBeingBorrowed

                                  If [ Assets::Quantity > Int ( 0 ) ]

                                  If [ Assets::QuantityBeingBorrowed > Int ( 0 ) ]

                                  Insert Calculated Result [ Assets::QuantityBeingBorrowed; Assets::QuantityBeingBorrowed - Int ( 1 ) ] [ Select ]

                                  End If

                                  End If

                                  ConfirmQuantityBeingBorrowed

                                  Set Field [ Assets::Quantity; Assets::Quantity - Assets::QuantityBeingBorrowed ]



                                  • 14. Re: Change Lending Library starter solution Process Check Out script to accommodate a Quantity field
                                    philmodjunk

                                    Somehow the value of Assets::QuantityBeingBorrowed is not being sent to History::Quantity. I'm not entirely clear why.

                                    That really is the issue all the way through here. If you have Filemaker Advanced, I suggest enabling the script debugger and data viewer just before testing these scripts so you can watch how each script is interacting with the data in your tables and with each other.

                                    Take a look at these steps:
                                    Set Field [ History::Quantity; Assets::Quantity ]
                                    Set Field [ History::Quantity; Assets::QuantityBeingBorrowed ]

                                    Note that they refer to data in Assests and then store the value in History. This only works if you have a valid relationship between History and Assets and if that relationship matches the correct record in Assets with the correct record in History.

                                    In the original script, this step: Set Field [ History::Asset ID; Assets One::Global Asset ID ]
                                    Should be establishing that link between a new record in History and the selected Asset record of the item(s) being checked out, provided Global Asset ID has global storage enabled like it should. However, you'll need to look at your relationships graph and make sure that it supports this. Given that your layout is based on History, you should see a direct link in Manage | Database | Relationships between a "box" labled "Assets" and another labled "History".

                                    History::Assets ID = Assets::Assets ID

                                    I realize you have tables of those names, but that doesn't guarantee you have this exact relationship and if one of these 'boxes' (Table occurrences) have been given a different name, it will keep these script steps from working until you update them to refer to the actual table occurrence names used in this relationship.

                                    If all of that looks correct, you may need to do as Fenton suggested and add a commit record just after this step: 
                                    Set Field [ History::Asset ID; Assets One::Global Asset ID ]

                                    This is also true for your AddOne and SubtractOne scripts. If the layout where you've placed these buttons, doesn't refer to "Assets" in Show Records From in Layout Setup..., then they aren't likely to work correctly. (Have you put QuantityBeingBorrowed on the layout so that you can see if the values are changing when you click the buttons?)

                                    1 2 Previous Next