7 Replies Latest reply on Nov 20, 2013 4:06 PM by philmodjunk

# Can anyone fix this case?

### Title

Can anyone fix this case?

### Post

whatever I'm doing wrong, it's not working.

field A should = 0,1,2,3,4,or 0 again if drop down choices for field B are either result1, 2,3,4,5, or 6 respectively.  I can't figure out how to return a result from the drop down without copying in the actual value.

Field A=

Case ( Field B = "result1" ; "0" ;
Field B= "result2" ; "1" ;
Field B = "result3" ; "2" ;
Field B = "result4" ; "3" ;
Field B = "result5"; "4" ;
Field B = "result6"; "0" ;)

Note:
result1 etc should be the command to return the first value from the list, not the copied value

• ###### 1. Re: Can anyone fix this case?

result1 etc should be the command to return the first value from the list, not the copied value

And what list is that?

And example with actual values from your value list in Field A with example results in Field B could be very helpful.

You might not even want to use a calculation for Field B as there are other options that can be a better approach in many cases.

• ###### 2. Re: Can anyone fix this case?

Let's say Field B = cereal, Field A = # of cereal boxes per unit, and I have a 3rd field to add up the sum of the number of boxes.  (I can't just do the SUM function on the row of field B's because it has more numbers in the drop down choices than just the # of boxes so the sum is off.

When I choose from the drop down menu in Field B, the corresponding choice should appear in field A

 Field B drop down choices Field A = triggered by field B drop down choices Field C = sum of all field As Kellogs cereal frosted flakes 474 # of boxes Kellogs 2 should = 4 when fruit loops and apple jacks are chosen fruit loops 221 3 capn crunch 392 4 General Mills apple jacks 7724 # of boxes General Mills 1 oat bran 266 5 cherrios 2829 7

*These are not accurate brands to the company names for anyone who might be nitpicky

• ###### 3. Re: Can anyone fix this case?
Assuming that your value list for Field B is:

frosted flakes 474
fruit loops 221
capn crunch 392
apple jacks 7724
oat bran 266
cherrios 2829

Then this case function will work for Field A:

Case ( Field B = "frosted flakes 474" ; 2 ;
Field B= "fruit loops 221" ; 3 ;
Field B = "capn crunch 392" ; 4 ;
Field B = "apple jacks 7724" ; 1 ;
Field B = "oat bran 266"; 5 ;
Field B = "cherrios 2829"; 7
) // case
But this does not look like a situation where such a calculation field is the best method to use. If it doesn't, make sure that Field B is of type text and that the text shown here is what the value list is actually entering into the field--it could enter an ID number.

If you set up a related table with this same data (each row below is one record)
Field B                  | Field A
frosted flakes 474 | 2
fruit loops 221      | 3
capn crunch 392   | 4
apple jacks 7724   | 1
oat bran 266         | 5
cherrios 2829       | 7

Then you could set up a relationship matching Field B of your original table to Field B of this related table and then you can use auto-enter field options to look up (copy) this data into Field A of your original table. This is how prices are looked up into Invoice Data records in the Invoices starter solution that comes with FileMaker 12. This method allows a user (instead of you, the developer) to change what values are looked up with each choice in your value list.

Field C should be a summary field in either case and your example seems made to order for a conditional value list where the first field is used to select either "Kellogs" or "General Mills" and then the second value list update to only list the brands for that specified company.

• ###### 4. Re: Can anyone fix this case?

Putting the case together like this worked.  Thanks so much.

Case ( Field B = "frosted flakes 474" ; 2 ;
Field B= "fruit loops 221" ; 3 ;
Field B = "capn crunch 392" ; 4 ;
Field B = "apple jacks 7724" ; 1 ;
Field B = "oat bran 266"; 5 ;
Field B = "cherrios 2829"; 7
) // case

• ###### 5. Re: Can anyone fix this case?

It works, but may not be the best option for this type of data manipulation.

• ###### 6. Re: Can anyone fix this case?

I'm really new to filemaker and really bad at it so far, so anything that does basically what I need is a miracle, but yes I'm sure the entire database I've created is full of awful workarounds for things that can be simpler and more efficient.

• ###### 7. Re: Can anyone fix this case?

Fair enough and we're here to help you improve on that.

Please keep in mind that your invoices starter solution provides a working example of the alternative approach that I described so you can open it up and examine how it works..