Calc to populate multi value checkbox set

[TravisB]

I have a database that consists of purchase orders (one record per PO) that will have a description assigned by a checkbox. I then have an "over view" sheet for each job that will give the end user a quick idea of what type of products are assigned to that job.

 

So, if a job has 5 purchase orders, and the descriptions for the purchase orders are "A, A, B, D, D" I would like the check boxes on the over view sheet to be checked as "A, B, D" with C and E remaining unchecked.

 

Also, a purchase order can be "A, C and D", or any other variation.

 

What I have done so far is assign for each purchase order a calculation to determine whether or not a value is checked (If (Scope = "A"  and From Bid  =  Bidding::Bid ID ; 1 ; 0)), then another summary field to check if any of the purchase orders for that job are assigned as "A", "B" etc, and then another calculation for the checkbox on the overview sheet:

 

If (Purchase Order::sA  ≤ 0 ; "" ; "A")  & ¶ &
If (Purchase Order::sB  ≤ 0 ; "" ; "B")  & ¶ &
If (Purchase Order::sC  ≤ 0 ; "" ; "C")  & ¶ &
If (Purchase Order::sD  ≤ 0 ; "" ; "D")  & ¶ &
If (Purchase Order::sE  ≤ 0 ; "" ; "E")
 

Which this works great if there is only one criteria checked per PO, but if there is one PO with more than one description it stops working, have "A" on one PO and "B" on another PO does seem to work just find though.

 

I know that its the "If (Scope = "A"  and From Bid  =  Bidding::Bid ID ; 1 ; 0)" part of it, but without filling out every possibility, which is a lot, I feel like I need a "wild card" option. This, however, also makes me wonder how the summary portion of my solution would work, or better yet if it would even work.

 

Any help with fixing what I have, or giving me a better way of doing it, is appreciated.

 

Also I should note that I know I could get this to work if I made a seperate check box for each description rather than a set, however that then creates 15 extra calculation fields in my database and 5 extra value lists and I feel like there should be a better way, not to mention I will have to deal with this exact situation on more than just this occasion for this database.

[eos]

Create a value list with the entries A,B,C,D,E, a calculation field List ( PurchaseOrders::Code ), put that on your layout and format it as control style checkbox with the new value list.

[Lee Smith]

It is not clear as to your purpose of your calculation, but it seems like you should be using a relationship and a Join table.

 

Have you looked at the example files that came with your FileMaker application, there is an example of an Invoice in them?

There have been several topics on this need over the years, try doing this search Invoices Do a search for Invoices.

 

In addition there are some video how to about invoices on YouTube Invoice Videos

 

Lastly, Todd Geist did a great video on this subject that you should also watch Geist

 

p.s. you can always attach a copy of your file so that we can see your structure.

Edited September 5, 2013 by Lee Smith
added the p.s.

[TravisB]

Create a value list with the entries A,B,C,D,E, a calculation field List ( PurchaseOrders::Code ), put that on your layout and format it as control style checkbox with the new value list.

This worked exactly as planned, thanks!

 

It is not clear as to your purpose of your calculation, but it seems like you should be using a relationship and a Join table.

 

Have you looked at the example files that came with your FileMaker application, there is an example of an Invoice in them?

There have been several topics on this need over the years, try doing this search Invoices Do a search for Invoices.

 

In addition there are some video how to about invoices on YouTube Invoice Videos

 

Lastly, Todd Geist did a great video on this subject that you should also watch Geist

 

p.s. you can always attach a copy of your file so that we can see your structure.

Thank you for the help. I dont see how invoices have to do with my questions, which my simply be because I haven't actually followed your advice yet, but I will certainly take a look if nothing more than to gain valuable unrelated info.

 

Thanks again to both - its very nice to have a resource as valuable as this forum.