AnsweredAssumed Answered

ExecuteSQL to obtain related field content

Question asked by GoodS on Apr 9, 2016
Latest reply on Apr 11, 2016 by user19752

Assuming a structure of:

Colour --< ColourInSet >-- ColourSet


We have all constructed a join table, to select Records from a related table.  Benefiting from controlling data consistency, compressing character overhead, through utilising key field referencing, is the reason we first adopted Filemaker.


The challenge occurs when we require field values from the referenced record other than the Primary key.

When selecting the primary keys through a portal, the order of their selection and the values themselves are extremely important.


Typical application:

Values selected in English, but upon language change to Chinese, or other symbol based references, another value needs to be calculated from the portal keys selected and in the sequence they were selected.

Values selected through long descriptions, although concise compilations of abbreviations are often needed for everyday use.

Build sheets require symbols to be adopted for everyday use, whereas the symbol names are selected, by their long description (short description, or other string, based upon the context) as part of the specific workflow process.


While we can store Chinese character, container filed, symbol, short name, abbreviation, or long name in the original Record, under the primary key, the simple concatenation of required fields from the primary key values selected through a portal is proving much more difficult to compile.


Pattern:

Handmade garments [or car/ bicycle/ yacht.. ] are easily described by their color combinations:

Main color/ overlay/ beading/ crystals..

Ivory/ Ivory/ Silver/ Clear

Short Color description: Iv ISCl

Each Color has three  [English] words, e.g. Ivory, Iv, I and White, Wh, W or Clear, Cl, Cl or Cafe, Ca, Ca

Note some colors have the same 'double' letter and 'single' letter abbreviation, whereas others have different values.  This requirement is specific to the application I have been commissioned to comply with.  Basically, value in field01, field02 ..


Result:

After we select a series of colors via a portal, how to construct Text field, such as: "Iv ISCl" (in ColourSet::c_Combo)

IE Double letter, space, Single letter..


Challenge:

After obtaining some assistance, I have been unable to obtain any calculated value from ExecuteSQL, other than "?", all records.

If we focus on value lists, only a single reference to a color is possible and sorting via PK in join table is also not possible, although in reality: Iv/ Iv/ Si/ Cl <> Iv/ Si/ Cl <> Cl/ Iv/ Si..  Imagine ordering a garment in "Ivory", only to have it supplied "Clear"


 

Message was edited by: Robert Lakelin Thank you Mike.  I have attached a file to clarify my question.

Attachments

Outcomes