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

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

### 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?

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

Pls help me out~

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

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!!!

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!!!

"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!!!

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

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 ; "" )

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!!!

"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!!!

@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!!!

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!!!

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.

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!!!

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

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!!!

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!!!

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!!!

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!!!

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.