14 Replies Latest reply on May 6, 2011 12:20 AM by leonhart1981

    Question about Calculation related to 4 different check boxes!!!

    leonhart1981

      Title

      Question about Calculation related to 4 different check boxes!!!

      Post

      Pls see the screenshot below: 

      Pls note that the grand total & the grand amount are sum of amount#1~#4 and total#1~#4

      Here is my question, I only want to sum up the grand amount and grand total when YES is checked

      In this case, what formula should I put? 

      Yes_or_No.jpg

        • 1. Re: Question about Calculation related to 4 different check boxes!!!
          leonhart1981

          Anyone know the answer?

          Pls help me out~

          • 2. Re: Question about Calculation related to 4 different check boxes!!!
            LaRetta_1

            We need to know how you are structured to answer this question.  Is #1Vendor, #2Vendor etc fields?  Are we viewing an Invoices table?  Does the yes/no checkbox plant 'yes' or 'no' within the field or does it hold 1/0 instead? 

            It feels like you have multiple fields for vendor, amount and total.  If this is the case then you are structured incorrectly and will hit brick walls at every turn.  If not, breathe a sigh of relief and present more information ... we can't provide any ideas without knowing what the underlying data, fields and relationships represent. :^)

            • 3. Re: Question about Calculation related to 4 different check boxes!!!
              philmodjunk

              Assuming that Vendor 1, 2, 3 and 4 represent 4 records in a list view layout and also assuming that the check box group is entering the text yes, no...

              I'd make the yes no field radio buttons. Selecting yes AND no for the same entry does make any sense here.

              With that change so that yes and no cannot be selected for the same field, use your sumary fields to compute totals of these two calculation fields:

              If ( RadioButtonField = "Yes" ; Amount ; "" )

              If ( RadioButtonField = "Yes" ; Total ; "" )

              (; "" can be removed from both of the above calculations and they'll still work.)

              If your value list is entering a number instead of the text, yes or no, you can simply modify the above calcualtion fields to check for those number values instead of "yes" or "no'.

              • 4. Re: Question about Calculation related to 4 different check boxes!!!
                LaRetta_1

                "Calculation related to 4 different check boxes!!!"

                Another giveaway that fields are being used here instead of records.

                • 5. Re: Question about Calculation related to 4 different check boxes!!!
                  leonhart1981

                  RE: LaRetta

                  Yes, it's a invoice table and the screenshot is a vender section under a tab.

                  Since one invoice's products may consist several venders, so I created 4 vender fields just in case. (vender#1 ~ vender#4)

                  And the YES & NO check box for each vender is the "Pay Status"  fields (also #1 ~ #4) which indicates whether the vender is been paid or  not.

                  So, basically the field structure is like:

                  Vender#1       V.Invoice#1       Amount#1                           Pay Status#1 YES    NO   (Radio button checked box field)

                                                              Discount#1

                                                              Total#1

                  ----------------------------------------------------

                  Vender#2      V.Invoice#2        Amount#2                           Pay Status#2 YES NO     

                                                              Discount#2

                                                              Total#2

                  -----------------------------------------------------

                  Vender#3 & vender#4's field structure are identically as above just # differences.

                  And the Grand Amount = Amount#1 + Amount#2 + Amont#3 + Amount#4

                              Grand Total = Total#1 + Total#2 + Total#3 + Total#4

                  But what I want to do for Grand Amount & Grand total is only sum up when the Pay Status' field's YES is checked.

                  In this case, what should I do...?

                  Re: PhilModJunk

                  Yes, you are correct. I should make the YES or NO to Radio Button instead. (Already made the adjustment)

                  But I didn't get it where should I put the below formula into which calculation field...?

                  If ( RadioButtonField = "Yes" ; Amount ; "" )

                  If ( RadioButtonField = "Yes" ; Total ; "" )

                  For your information,

                  Total#1 = Amount#1 - Discount#1

                  Total#2 = Amount#2 - Discount#2

                  and etc....

                  • 6. Re: Question about Calculation related to 4 different check boxes!!!
                    LaRetta_1

                    "Since one invoice's products may consist several venders, so I created 4 vender fields just in case. (vender#1 ~ vender#4)"

                    And what are you going to do if there are five vendors?  My point is that your structure is incorrect.  You have four vendors so you must have four totals, four checkboxes, four times the work to find a specific vendor and now, if you continue, you will have four NEW calculations to accumulate those four 'sets' of information.  Proper reporting will be impossible to summarize by vendor.

                    It is obvious that you are not too far into this project.  I am deadly serious ... change it now.  You will eventually HAVE to change it.  If you wait until you run into the next problem, you will have four times the work to do to change it then.

                    • 7. Re: Question about Calculation related to 4 different check boxes!!!
                      philmodjunk

                      @leonhart1981

                      As LaRetta already pointed out, my calculations won't help you out if you have multiple fields where you should have multiple records. Consider placing a portal to a related table here in your tab. That will work with what LaRetta and I are suggesting that you use here and will allow you to have as many vendors as you need instead of being limited to a maximum of 4 vendors.

                      • 8. Re: Question about Calculation related to 4 different check boxes!!!
                        leonhart1981

                        OK~ so I made a mistake and need to change it~

                        But how do I change it?

                        Should I create a new table just for venders and use relationship to link invoice & vender tables together?

                        Sorry for the trouble though! I'm kinda a newbie here....

                        • 9. Re: Question about Calculation related to 4 different check boxes!!!
                          LaRetta_1

                          No prob being a newbie - we all were at one time; I still feel like one after a decade of working with FM every day.  Creating fields instead of records is probably the most common error when first learning FileMaker. Laughing

                          I cannot recommend because I do not know what you have.  Do you have a LineItems table where each product purchased for an invoice is listed? And, if one product can be purchased from different vendors then you need a vendors table as well.  Are you using unique IDs in your tables?

                          I would be of disservice if I tried to provide answers to partially-formed concepts.  Can you clone an empty file with what you have, upload it somewhere and then provide a link here?  If not, please create a simple FM example file of how you are relating Customers, Invoices, LineItems and Products.  This sample will allow us to suggest what needs to be modified to normalize your data.

                          UPDATE:  A properly structured solution will be 50 times easier to work in than how you were progressing so don't be discouraged by it sounding complex.  It will seem strange at first but very quickly, a lightbulb will go off and you will be designing relationally and tickled that you took the time to understand it.

                          We all have had someone take the time with us working through these issues so don't feel you are a trouble; that is why we are here ... to help others like we have been helped.

                          • 10. Re: Question about Calculation related to 4 different check boxes!!!
                            leonhart1981

                            Hello! Sorry for the waiting. I have made some changes on the structure using relationship. 

                            I also upload a sample of my invoice & vender and a picture for your reference. Here is the website for download. 

                            I would like to do the following stuff but kinda stuck

                            1. I would like to sum up the V. Total only when "We Paid" is checked. 

                            2. I want to have the Balance to deduct the V. Total amount when the vender is DC

                            For example, the 2nd record in the CD sample, there are 3 venders and the Balance is $4,000. I want to have the Balance to deduct DC's V. Total which would be $4,000 - $2,000

                            3. Also, I am trying to create a report layout with subtotals & grand totals included. But both V. Inv. Amount & V. Total didn't allow me to select them. Pls refer to the file "Creating Report layout question" for more details. 

                            Pls help me out how to make it works. 

                            • 11. Re: Question about Calculation related to 4 different check boxes!!!
                              philmodjunk

                              for number 3, you may find this tutorial helpful once you get your table structure and relationships worked out: 

                              Creating Filemaker Pro summary reports--Tutorial

                              • 12. Re: Question about Calculation related to 4 different check boxes!!!
                                leonhart1981

                                OK~ I read through the tutorial and kinda understand how to make a report.

                                But how about Qeustion #1 & #2 ?

                                • 13. Re: Question about Calculation related to 4 different check boxes!!!
                                  philmodjunk

                                  Like LaRetta, I am waiting to get a clearer picture of how you have currently set things up. That's why I suggested looking at the tutorial, "once you get your design issues figured out".

                                  • 14. Re: Question about Calculation related to 4 different check boxes!!!
                                    leonhart1981

                                    Thank you for you ppl's help~

                                    After make my tables relationship to each other and play around a bit, I kinda figure out how to make the report layout that I want.