Calculation results read as field names (instead of text values)

[polarpro]

Hi,

I wonder if I can calculate a "shortcut" in one of my calc fields. Currently, I'm using this expression to get certain field values, based on the field Category:

• Case (
  Category = "Person" ; BAS_PER::LemmaPER ;
  Category = "Organization" ; BAS_ORG::LemmaORG ;
  Category = "Geographic" ; BAS_GEO::LemmaGEO ;
  ... )

Instead of using the Case function I'd rather calculate the name of the result field like

• 
  "BAS_" & Left ( Category ; 3 ) & "::Lemma" & Left ( Category ; 3 )

(I'm ignoring UpperCase here to make the function easier to read.)

The result I get unfortunately is

[color:red]"BAS_GEO::LemmaGEO[color:red]" and not BAS_GEO::LemmaGEO ,

so, what I want as field name is treated as text.

Is there a way to have Filemaker read my result as field name? (Using FMA 9)

Thanks a lot

: Mike

[comment]

Check out the GetField() and Evaluate() functions.

BTW, one cannot help wondering why all your lemmas aren't in a single table.

[polarpro]

Hey Mike,

Thank you for pointing out the right direction. This is how my calculation now looks like:

• Let (
  CatAbbr = Upper ( Left ( Base::Category; 3 ) ) ;
  GetField ( "BAS_" & CatAbbr & "::Lemma" & CatAbbr )
  )

(CatAbbr = Category Abbreviation) (And, as you see, I put the Upper function in there.) Replacing GetField with Evaluate gives the same result.

BTW, one cannot help wondering why all your lemmas aren't in a single table.

This is because of the database structure. The information common to all records is stored in a single big Base table (BAS), with relationships to other tables based on "category" of data that hold the other pieces of information. E.g. the Persons table (PER) holds fields like Prename, Surname, Title; fields that are not needed in other tables. So, one database entry actually consists of two records, one in BAS, and one in PER / ORG / GEO / ...

The reason behind this is that I want to allow users to create relationships between entries of different categories, e.g. organization – person.

So far, this structure has worked fine, but I admit, it makes some things go definitely slower. This can be seen e.g. in particular in filtered portals.

The only alternative I can see is putting everything in one table, using self-join relationships in order to relate different records to each other. Hm. Suggestions welcome.

Thanks again!

[comment]

Well, I don't mean to delve too deep into this - it just seems strange that a common field (it is a common field, isn't it?) needs three variations.

[polarpro]

No, not really.

For example, in the case of an organization the field content of LemmaORG is what the user keys in.

But in the case of a person LemmaPER is a calc field:

LemmaPER = PrenameBindingPER & " " & SurnameBindingPER.

I don't mean to delve too deep into this

: Am actually preparing a new thread on this, as mentioned: Some filtered portals are too slow for my taste.