1 2 Previous Next 24 Replies Latest reply on May 16, 2012 6:17 PM by liza

    Tips to simplify this script


      Hi, I'm making a POS with FMP 11. I have a Style table, an Item table (containing different sizes per style) a Location table (with the different stores), and an ItemLocation table, where I should see the inventory of each item per location.


      I'm making a script where, from the Style table, I can create records of each item in each location. So basically this script gets the Item IDs per style, then gets the Location ID, then creates a record in ItemLocation table. I'm attaching the script steps below. I'm just wondering if anyone can suggest a way to simplify this script? Each style has around 10 items, and I currently have around 20+ locations. So it takes about a minute to run this script, which is a bit long, considering I currently have 300+ styles and we regularly add a lot of new styles. The script works well, just concerned about the performance. Or maybe is there a simple way to be able to just choose which locations I will add the styles to?




        • 1. Re: Tips to simplify this script

          Hi, Liza.


          Well, my first observation would be you seem to have a three-way join table here between Style, Item, and Location. Assuming that's correct, you're needing to create join records on an automated basis. Your current script is using nested loops to accomplish this. It works, but, as you've noticed, it takes a bit of time to complete. It will get worse as you add more records. (And you're to be commended for noticing the issue NOW, rather than waiting until your users start complaining!)       


          I'm going to suggest two alternative methods to you. You can pick and choose and see which works better for you.


          Method 1: This involves using Import and Replace Field Contents. It's a bit old school, but you can start off basically the same way you already have. Find all the needed Location records, then use Import to put the relevant fields into ItemLocation. Then use Replace Field Contents to populate Style ID and Item ID. This works quite well, but carries a little bit of a risk. Replace Field Contents is FileMaker's nuclear weapon. If something goes wrong, you can wipe out large record sets and end up with a mess. Since you're creating new records, the risk is minimized, but some developers prefer to stay away from Replace because of its risks (and irrecoverable nature).


          Method 2: This involves assembling your key fields into return-delimited lists in variables, then simply looping through the lists and creating the new join records as you go. You can do this using the native FileMaker List ( ) function, or using a custom function such as this one from Brian Dunning's site: http://www.briandunning.com/cf/439. Use GetValue ( ) and just loop through the lists once and create your new records as you go.


          Method 1 might be a little faster, but might need more error checking to make sure you don't wipe something out you shouldn't (and to make sure you're not going to collide with another user). Method 2 is safer.





          • 2. Re: Tips to simplify this script

            Hi Mike,


            Thanks a lot for the reply. I think I'd rather go with the safe route. I'm still quite new to Filemaker, and I don't have any programming background, so right now, a lot of the scripts I make don't always go according to plan.


            If it's not too much trouble, can you give a sample of the script steps to use for Method 2 just to give me a guide on how to start? I'm having a hard time figuring out how to go about it.


            Thanks and regards,


            • 3. Re: Tips to simplify this script

              Sure, Liza. Let me ask this: Do you have the same number of locations, items, and styles when you create these records, or do they vary?

              • 4. Re: Tips to simplify this script

                Locations are usually the same number, but the items vary. The script is located in the Style table, so for each style, I run the script, and it gets the items (the sizes of that style) which may have a different number in every style, and the Locations, which is the same number everytime, unless we open a new store and add to that number.

                • 5. Re: Tips to simplify this script

                  Okay. That being the case, we're probably going to end up with nested loops again, but we should be able to speed it up anyway. Pseudo-code might look something like this:


                  Set Variable [ $StyleID ; _kp_StyleID ]

                  New Window [ "Item List" ]

                  Go to Layout [ "Item List" ]

                  Perform Find [ Restore ]

                  (should look familiar to this point)   

                  Set Variable [ $ItemIDs ; GetNthRecordSet ( _kp_ItemID ; 1 ; "" ) ]  // Set a return-delimited list of all Item IDs you want to save

                  Go to Layout [ "Location List" ]

                  Show All Records

                  Set Variable [ $LocationIDs ; GetNthRecordSet ( _kp_LocationID ; 1 ; "" ) ]  // Set a return-delimited list of all Location IDs you want to save

                  Go to Layout [ "Item Movement Per Location" ]

                  Set Variable [ $itemCount ; 1 ]


                    Set Variable [ $locationCount ; 1 ]


                      New Record / Request

                      Set Field [ ItemLocation::_kf_StyleID ; $StyleID ]

                      Set Field [ ItemLocation::_kf_ItemID ; GetValue ( $ItemIDs ; $itemCount ) ]

                      Set Field [ ItemLocation::_kf_LocationID ; GetValue ( $LocationIDs ; $locationCount ) ]

                      Set Variable [ $locationCount ; $locationCount + 1 ]

                      Exit Loop If [ $locationCount > ValueCount ( $LocationIDs ) ]

                    End Loop

                    Set Variable [ $itemCount ; $itemCount + 1 ]

                    Exit Loop If [ $itemCount > ValueCount ( $ItemIDs ) ]

                  End Loop

                  Close Window [ "Item List" ]


                  Now, this assumes a couple of things. I'm assuming you need a record for each combination of Item and Location. I'm also assuming you only have a single style that needs to be added.


                  But note a couple of other changes. There's no need for a Commit Records / Requests script step after you create each new record. That'll happen each time you create a new record (the previous one will be automatically committed anyway). I'm not sure what the Find is before you start creating records in your script, but if you're checking to see if there are already records for that Style, you can accomplish that a lot quicker by doing this:


                  If ( not IsEmpty ( ItemLocation::_kf_StyleID ))

                    Exit Script

                  End If


                  before you start your script. That way, you don't spend time checking during your script, after you've already done all those other Finds and opened all those other windows.


                  Speaking of which, you really don't need all those extra windows. They consume memory and compute cycles, and since you can accomplish everything in a single utility window, I suggest just doing that.


                  Anyway, that's just a quick and dirty stab. Hope it helps. Please let me know if you have questions.



                  1 of 1 people found this helpful
                  • 6. Re: Tips to simplify this script

                    Thanks Mike, I'm still trying out the steps below. Some concerns though: I think GetNthRecordSet is a custom function? I'm not using FMP Advanced, so how do I go about this using the regular functions?


                    You're right, the Find looks for existing records with that particular StyleID, ItemID and LocationID. And it probably does contribute a lot to the slow process. If I use the script you suggested though:

                         If (not IsEmpty (ItemLocation::_kf_StyleID ))

                         Exit Script

                    Won't that just look at the styleID? I need to ensure there are no records yet with the particular ItemID and LocationID as well.

                    • 7. Re: Tips to simplify this script

                      See if anyone an install that custom function for you. After reading this thread. Used it this morning, and it Completed my process.


                      I can put it in for you if you can give me remote access tomorrow or if you can send me the file.


                      Message me back channel if interested.





                      agnes b. riley . filemaker and web development


                      T 201-299-6223 (NJ) . 212-842-8830 (NY) . 917-660-7221 (C)

                      FileMaker Certified in 10 and 11

                      1 of 1 people found this helpful
                      • 8. Re: Tips to simplify this script

                        Thanks Agi, I appreciate the offer. Will send you a message.

                        • 9. Re: Tips to simplify this script

                          Hi Liza. Agi has already dealt with the custom function issue. As far as locating existing records, I'm a little confused. You're creating a large number of records with varying Item and Location IDs. A simple Find with restored requests won't locate those for you. What are the Find requests in your existing scripts?



                          • 10. Re: Tips to simplify this script

                            Hi Mike, I just find the item id and location id. If it's already existing, the script beeps, and continues. But if it doesn't, then it creates a new record. See attached screenshot.




                            Screen shot 2012-05-11 at 6.28.21 PM.png

                            • 11. Re: Tips to simplify this script

                              What happens if that item and location already exist, but for a different style?

                              • 12. Re: Tips to simplify this script

                                Hi Mike, each itemID is unique. Let's say StyleID # 1 can have the itemIDs 1,2,3 and 4. StyleID # 2 will have the itemIDs 5, 6, 7 and 8. and so on. So an itemID always belongs to just 1 style.


                                To give an example:


                                Style Table

                                StyleID: 1

                                StyleID: 2


                                Item Table

                                ItemID: 1 (StyleID: 1, Size: 1y)

                                ItemID: 2 (StyleID: 1 Size: 2y)

                                ItemID: 3 (StyleID: 1 Size: 3y)

                                ItemID: 4 (StyleID: 2 Size: 1y)

                                ItemID: 5 (StyleID: 2 Size 2y)

                                ItemID: 6 (StyleID: 2 Size 3y)


                                Location Table

                                LocationID: Store1

                                LocationID: Store2


                                ItemLocation Table

                                ItemLocationID: 1 (StyleID:1, ItemID: 1, LocationID: Store1)

                                ItemLocationID: 2 (StyleID:1, ItemID: 1, LocationID: Store2)

                                ItemLocationID: 3 (StyleID:1, ItemID: 2, LocationID: Store1)

                                ItemLocationID: 4 (StyleID:1, ItemID: 2, LocationID: Store2)

                                ItemLocationID: 5 (StyleID:1, ItemID: 3, LocationID: Store1)

                                ItemLocationID: 6 (StyleID:1, ItemID: 3, LocationID: Store2)

                                ItemLocationID: 7 (StyleID:2, ItemID: 4, LocationID: Store1)

                                .. and so on




                                • 13. Re: Tips to simplify this script



                                  Okay, that being the case, let's see ...


                                  One way to avoid the repeated Find requests (which will definitely slow you down) is to create another table occurrence for ItemLocation. Then put a global field in Style. Create a concatenated key in ItemLocation, like this:


                                  StyleID & "-" & ItemID & "-" & LocationID


                                  Then, during your script, set the global field in Style equal to a return-delimited list of all combinations of StyleID, ItemID, and LocationID. It'll look like this (as an example):


                                  1 - 1 - 1

                                  1 - 1 - 2

                                  1 - 2 - 1

                                  1 - 2 - 2

                                  1 - 3 - 1

                                  1 - 3 - 2

                                  2 - 4 - 1




                                  Make a relationship between the Style table and the new TO of ItemLocation (you can call it ItemLocationGlobal or something). The relationship should be the global field on the left and the concatenated key on the right. Then, your test will look like this:


                                  If ( ValueCount ( { globalfield } ) = Count ( ItemLocationGlobal::{concatenated key} )

                                    Exit Script

                                  End If


                                  You'll still have to deal with the case where there are some matching records, but not the same number (what happens if a join record gets deleted, leaving you with one missing join record?). In cases like this, I usually like to set up a routine to find and delete duplicates just in case.


                                  But I'm paranoid.   






                                  Edit: Oh - forgot to mention. I have some custom functions that allow you to do things like add and remove columns from return-delimited lists. Message me back channel.

                                  • 14. Re: Tips to simplify this script

                                    Hi Mike, with Agi's help on the custom function, I was already able to try out the first script you suggested, and it worked beautifully, definitely ran much faster than my original one, thanks so much! This is without the script to find duplicates yet though. I tried that out, and it doesn't seem to be working, but I'm probably doing something wrong. Will send you a message regarding the script and the custom functions too.

                                    1 2 Previous Next