Calculation to input text value based on checkbox values in other field

[ShannonM]

Hi, I am new here so please bear with me.

Ok, I have a Province table which contains two fields - region and provinces. Each province belongs to one of two regions (Oriental or Palearctic). There 9 different provinces.

In a related table I have the related province field presented as a check box set based on a value list from the Province table. I then have the related Region field which I would like to set up as a calculation field. I am trying to get a text value (Oriental or Palearctic) to auto-enter based on the Province values selected in the check-box set of the Province field. I hope this makes sense...

So I set up the Region field as a calculation.

I tried an "if" statement first.

So,

Region=

If(ChineseProvince="Hainan";("Oriental"))

This worked if I only had one value selected in the Chinese Province field. However, I need it to work if more than one province is selected. For example, "Hainan" and "Guangxi" should give the "Oriental" result.

I also tried a case statement listing the possible provinces that would yield "Oriental" but that didn't seem to work.

Does anyone have any suggestions as to how I set up to recognized multiple values and yield one result?

Thanks!

[Søren Dyhr]

Try this:

Case ( ValueCount ( FilterValues ( entry;"Hainan¶Guangxi") );"Oriental" )

--sd

[comment]

I don't get this: what if I select two provinces, one Oriental and one Palearctic? What should the result be then?

[ShannonM]

Then the result should be "Oriental and Palearctic"...ahhh, that just creates more of a problem doesn't it?

[ShannonM]

Thanks! That works!

Now - to make things more complicated:

1) How do incorporate the other region option "Palearctic" for the other provinces choices. Also, it is possible to have the result be "Oriental and Palearctic" if provinces from both cases are checked?

[comment]

How about something like this?

Provinces.fp7.zip

[Søren Dyhr]

You can make a chain of my statements and concatemate them with &.... But the result in the second, and beyond should the word "and" as well as some surrounding it before the category be put inside the Case( statements result part.

--sd

[ShannonM]

Thanks so much for your help.

That works beautifully!