14 Replies Latest reply on Jul 9, 2009 8:52 AM by thong127

    pop up alarm in reorder level

    thong127

      Title

      pop up alarm in reorder level

      Post

      Hi to all members of the forum,

       

      I'm new to filemaker, and I'm doing a materials log book, and I have a field "Reorder Level" kindly please help me making a script or calculation that when a certain or list of materials reached a certain level of percentage like for example 10 % of the total number of materials filemaker will pop up a dialog box telling that "These materials needs to be re-order".

       

      Thank you very much in advance to any help that you can extend.

       

      More Power to all members of the forum.

       

      Thong

       

       

        • 1. Re: pop up alarm in reorder level
          etripoli
             Are you running FM10?  Does the user have to be on a certain layout, and accessing a record(s) that have items at/below their reorder level?
          • 2. Re: pop up alarm in reorder level
            ninja
              

            Howdy Thong,

            Welcome to the forum.

             

            It would make sense to me that the "Reorder Level" and "Current Stock Level" would be on the same layout.  If you add a "GetMore" field on that layout you should have what you're after.

             

            The "GetMore" Field would be a calculation resulting in text, something of the sort:

             If (CurrentStockLevel {lessthan} ReorderLevel; "Get More" ; "")

             

            You could make the font larger than normal and turn it bright red so it would be hard to miss on the layout.

             

            Additionally, entering find mode, typing "More" into that field and hitting return will give you a found set of stuff you need to buy (a shopping list).

             

            To use a percentage, as you propose as an option, it could be:

            If (CurrentStockLevel {Lessthan} 0.1 * BaseStockLevel; "GetMore"; "" )

             

            I would recommend a defined reorder point rather than 10% of something else, but that has nothing to do with Filemaker.

            Is this what you're after?

            • 3. Re: pop up alarm in reorder level
              thong127
                

              Hi Ninja,

               

              Thank you for your immediate reply, I would like to attach my data base to the forum so that you can figure out and help me more about my problem. How can I attach my Data Base?

               

              Again, thank you very much to your reply.

               

              Thong

              • 4. Re: pop up alarm in reorder level
                thong127
                  

                Hi Ninja and to all members of the forum,

                 

                These are some of the contents of my dataBase

                 

                Product Name 

                Expiration Date

                Quantity Received

                Quantity Used

                Quantity Balance

                 

                On the quantity balance I made a calculation "Quantity received - Quantity Used" and my problem is I would like to make a script for a pop up reminder that will remind me if the quantity balance of any material reached to a certain level (10% level) based from the Quantity received.

                 

                I have a script for the reminder based from a birthdate reminder which I have read.

                 

                1. Enter find mode []

                2. Set Field[Reorder:: Quantity Balanced: If(Reorder::Quantity Balanced {lessthan or equal   to} .1*Reorder::Quantity Received;""]

                3. Performe Find[]

                4. Set variable[$found;Value:Get( FoundCount )]

                5. If [$found>0]

                6. Set variable [$counter; value:1]

                7. Loop

                8. Set Variable[$names; Value:$names & GetNthRecord(Reorder::Product Name ; $counter ) & "¶"]

                9. Exit Loop If($counter = $found]

                10. Set Variable [$Counter; value:$counter + 1]

                11. End Loop

                12. Show Custom Dialog["Attention! Reorder Products"; "Products Need to Reorder:¶" & $names]

                13. End If

                 

                Kindly help me organize the script based from my data.

                 

                Thank you very much.

                 

                Thong

                 

                • 5. Re: pop up alarm in reorder level
                  thong127
                    

                  Hi etripoli and to all members of the forum,

                   

                  Thank you for your reply, Im using FM10

                   

                   

                  These are some of the contents of my dataBase

                   

                  Product Name 

                  Expiration Date

                  Quantity Received

                  Quantity Used

                  Quantity Balance

                   

                  On the quantity balance I made a calculation "Quantity received - Quantity Used" and my problem is I would like to make a script for a pop up reminder that will remind me if the quantity balance of any material reached to a certain level (10% level) based from the Quantity received.

                   

                  I have a script for the reminder based from a birthdate reminder which I have read.

                   

                  1. Enter find mode []

                  2. Set Field[Reorder:: Quantity Balanced: If(Reorder::Quantity Balanced {lessthan or equal   to} .1*Reorder::Quantity Received;""]

                  3. Performe Find[]

                  4. Set variable[$found;Value:Get( FoundCount )]

                  5. If [$found>0]

                  6. Set variable [$counter; value:1]

                  7. Loop

                  8. Set Variable[$names; Value:$names & GetNthRecord(Reorder:

                  9. Exit Loop If($counter = $found]

                  10. Set Variable [$Counter; value:$counter + 1]

                  11. End Loop

                  12. Show Custom Dialog["Attention! Reorder Products"; "Products Need to Reorder:¶" & $names]

                  13. End If

                   

                  Kindly help me organize the script based from my data.

                   

                  Thank you very much.

                   

                  Thong

                  • 6. Re: pop up alarm in reorder level
                    etripoli
                      

                    Step # 2 doesn't look right.  It should be:

                     

                     

                    2. Set Field[Reorder:: Quantity Balanced: ">= " & .1*Reorder::Quantity Received]

                    • 7. Re: pop up alarm in reorder level
                      philmodjunk
                        

                      2. Set Field[Reorder:: Quantity Balanced: ">= " & .1*Reorder::Quantity Received]

                       

                      won't work either unless Reorder::Quantity Received is a global field and this quantity is the same for all records. That's why ninja suggested a calculation field that returned text when a specific item reached its re-order point. You can then use that text in your find request to pull up all records for items that need to be re-ordered.

                      • 8. Re: pop up alarm in reorder level
                        etripoli
                          

                        You're right.  How about this?

                         

                        With a Materials table, that contains items to make a product, and a Products table, that contains the finished products:

                         

                        Create a calculated field in the Materials table, "Need Reorder: case ( .1 * last ( Reorder::Quantity Received ) >= Reorder::Quantity Balanced, 1 )"

                        Create a calculated field in the Products table, "Reorder Needed: case ( sum (Materials::Need Reorder ) >0, "Items need reorder" )

                        Then run a script that finds all the records in Products  that have "Items need reorder" in the 'Reorder Needed' field.

                        • 9. Re: pop up alarm in reorder level
                          ninja
                            

                          howdy thongLoreto,

                           

                          You don't need the script to calculate things at all.  You need a calculation field.

                          A new calculation field (named "GetMore") would be defined as:

                           

                          If(Reorder::Quantity Balance {lessthan or equal   to} .1*Reorder::Quantity Received;"Products Need to be Reordered" ; "")

                           

                          If you want to use your script to make a "custom dialog" shopping list (Not Recommended), start you script with:

                          1. Enter find mode []

                          2. Set Field[Table1::GetMore; "Products Need to be Reordered"]

                          3. Perform Find[]

                          4. Set variable[$found;Value:Get( FoundCount )]

                          5. etc. etc.

                           ++++++++++++++++++++++++++++++

                           

                          I, personally, would create a new layout (named Reorder List) in list view that shows the results of your find instead of using the custom dialog box.

                           

                          If you go this way, your script would simply be:

                           

                          1. Enter find mode []

                          2. Set Field[Table1::GetMore; "Products Need to be Reordered"]

                          3. Performe Find[]

                          4. GoToLayout [ReorderList]

                           

                          Am I making sense about not needing the script to calculate what items need to be ordered?

                          • 10. Re: pop up alarm in reorder level
                            thong127
                              

                            Thank you very much for your promt assistance and reply

                             

                            I have already change my step number 2 in my script but it says "There are no valid Criteria in this request. type a valid request before clicking find" is there a wrong with my field?

                             

                            Quantity Received     Text            Global

                            Quantity Used           Number        Global

                            Quantiy Balance        Calculation    Unstored, = Quantity Received - Quantity Used

                             

                            • 11. Re: pop up alarm in reorder level
                              etripoli
                                

                              Hmm, I think there might be a problem in your overall database design.  I would see that you need at least 2 tables:

                               

                              Table 1: Materials (one unique record per material)

                              Material Name: Text

                              Needs Reorder: case ( ( sum (Reorder:Quantity Received) - sum (Reorder:Quantity Used))/last (Reorder:Quantity Received) <= .1, "Needs Reorder" )

                               

                              Table 2:Reorder (one record each time a material is used or received)

                              Material Name: Text

                              Date Received: Date

                              Quantity Received: Number

                              Date Used: Date

                              Quantity Used: Number

                               

                              • 12. Re: pop up alarm in reorder level
                                thong127
                                  

                                Is there a way that I can attach my data base to the forum for your review?

                                • 13. Re: pop up alarm in reorder level
                                  etripoli
                                    

                                  thongLoreto wrote:

                                  Is there a way that I can attach my data base to the forum for your review?


                                   

                                  Place it on a publicly accessible web/ftp server, and provide the URL.


                                  • 14. Re: pop up alarm in reorder level
                                    thong127
                                      

                                    This is the link of my database "rapidshare.com/files/253840704/MaterialLog.fp7.html" hope you can help me building the script that will give me a pop up alarm when  a material reached its 10 percent re-order level like my first script that it will give me a pop up alarm using a dialog box showing the materials expiring for the next month.

                                     

                                    Thank you very much.