7 Replies Latest reply on Feb 27, 2011 6:44 AM by Ween

    Calculation

    thong127

      Title

      Calculation

      Post

      Hi to Everyone,

       

      Kindly Help me please to my problem, I have fileds:

       

      1. Material Name

      2. Quantity Received,

      3. Quantity Balance,

      4. Quantity Status (Active, Expired)

       

      I have also a Field, I called it Total Quantity Received with a type of Summary (= Quanity Received (running with restart) when sorted by Material Table:: Quanity Received) and Total Quantity Balance with a type of Summary (= Quanity Balance (running with restart) when sorted by Material Table:: Quantity Balance).

       

      My Problem now is, I want that when I search the Material Name the Total Quantity Balance will automatically disregard or subtract the number or amount of the materials received which are Expired. Kindly help me to figure out the calculation.

       

      Thank you very much.

       

      thong

        • 1. Re: Calculation
          philmodjunk
             What does your quantity balance calculation look like?
          • 2. Re: Calculation
            thong127
              

            My Quantity Balance looks like this (Indexed, from Materials Table, = Quantity Received - Quantity Used).

             

            thong

            • 3. Re: Calculation
              philmodjunk
                

              This is the part of your original post that I am puzzling over. I can read it more than one way:

               

              "My Problem now is, I want that when I search the Material Name the Total Quantity Balance will automatically disregard or subtract the number or amount of the materials received which are Expired. Kindly help me to figure out the calculation."

               

              Do you want the calculation to compute a different result when you "search the material name"? Do you need a second calculation that reads like this:

               

              IF ( Quantity Status = "Expired" ; 0 ; Quantity Received - Quantity Used )

               

              Perhaps that will be the new definition for Quantity Balance?

              • 4. Re: Calculation
                thong127
                  

                I'm sorry for making you a little bit confuse.

                 

                Do you want the calculation to compute a different result when you "search the material name"? My answer is Yes Please.

                 

                I want that if I search the Material (Because I have many materials having the same name but different in Lot No. some are expired and some are not), in my Total Quantity Balance it will automatically subtract the quantities of the expired materials.

                 

                Thank you for your help.

                 

                thong

                • 5. Re: Calculation
                  philmodjunk
                    

                  If you define a new field named "Balance Active" and define it as IF ( Quantity Status = "Expired" ; 0 ; Quantity Received - Quantity Used )

                   

                  A summary field defined as the "Total of" Balance Active will return a total that ignores all "expired" entries.

                   

                  Does that do what you need?

                  • 6. Re: Calculation
                    thong127
                      

                    Thank you very much PhilModJunk, It works. Thanks a lot.

                     

                    thong

                    • 7. Re: Calculation
                      Ween

                      Hello

                      I hope you guys can help me here. PhilModJunk has helped me many times and thank you for that. I am a filemaker novice at best. I have created a database for my a youth football league. I am trying to figure out out to carry a balance for a player who hasn't made all of his/her required paments. As seen in the sample below. I want to be able to let this person make another payment (creating a new record) and carry over the balance. I have a player table and a payment table with a relationship in the "player" field.

                      Thank you in advance!

                      Ween

                      For the total calculation i have this: league_fee + jersey_fee + raffles + buyout + late fees

                      For the Balance I have this:  payment amount - total

                      screen grab from filemaker pro v11