Calculation Syntax

[acro]

I want to make this field (PO# Field) a calculation field to show results based on fields: Facility field and Equipment field. For example if Facility = "Mercy Hospital" and Equipment = "mattress", result should be: PO2222, but if Facility= "St John Hospital" and Equipment= "mattress", result should be PO4444.
Question is: what would be the syntax for the calculation on the PO# field?

 

Please see Picture attached

 

Using FMP 13 on PC

[comment]

The two examples you have given do not explain the logic by which such calculation should proceed. I suspect you really want to do a lookup here, but that's just a guess. You should explain in more detail.

[eos]

Question is: what would be the syntax for the calculation on the PO# field?

 

Well, you almost spelled it out already:

Case (  Equipment = "Mattress" ;
  Case ( 
    Facility = "Mercy Hospital" ; "PO2222";
    Facility = "St John Hospital" ; "PO4444"
) )

Let me add that while this is the correct syntax for this exact requirement, you probably will want to use an Inventory table in which each record describes a piece of equipment for a facility and has a PO#; then you would be able to look up that PO via a relationship based on those two fields (preferably using equipment and facility IDs instead of names).

 

EDIT: What happened to the "New Post" alert that used to pop up while writing a reply?

[acro]

These are not two examples, they are the same example expressed twice.

However what I'm trying to do should be very simple for anyone with experience in FMP. Lets say we have 3 fields in a data base: number1 Facility field, number2 Equipment field and number3 PO# field.

The goal is to make PO# field a calculation field that will automatically populate results based on the following calculations:

If field number1 (Facility field) reads "North facility" and field number2 (Equipment field) reads "chair", then field number3 should read let's say "PO1111"

But If field number1 (Facility field) reads "South facility" and field number2 (Equipment field) reads "chair", then field number3 should read let's say "PO2222" <--- (These are just examples)

Question: what syntax in the calculation field will achieve this?

[eos]

However what I'm trying to do should be very simple for anyone with experience in FMP

 

Which according to your Skill Level you should have … … but then again, it is likely not the right way to go about this.

 

Question: what syntax in the calculation field will achieve this?

 

Why don't you read the replies, e.g. in post #3?

 

Anyway, this is not a question of using the correct syntax, it's a question of the correct data structure.

 

Instead of coding any number equipment/facility combinations to produce a result, you should have a table in place where each facility/equipment combination and its associated PO is encoded as data. Then a simple lookup via a relationship will give you the desired result just by referencing the related PO field, allowing you to add any number of combinations without having to modify the lookup mechanism / calculation field.

[acro]

Thanks a lot eos, and yes, the comments you just added are exactly what I'm planning to do. Good stuff appreciate it!