7 Replies Latest reply on May 24, 2016 2:36 PM by beverly

# Calculating fields and sorting

Hoping for some help to this problem!

I have a Child table with a Value and Name.

5 | Name1

2 | Name2

75 | Name3

50 | Name4

and so on...

How can I display a parent field shown this way? (high to low):

75% Name3, 50% Name4, 5% Name1,  2% Name2, and so on...

THANKS!! =)

• ###### 1. Re: Calculating fields and sorting

Presuming you are showing a Portal with these "children" records and the two fields "value" and "name".

You can SORT the portal (by the related field "child::value") and specify DESCENDING.

if, on the other hand you used an ExecuteSQL() calc to get the values & names, you can ORDER BY to 'sort' the values. This can also be DESC:

SELECT value, name

FROM childTable

WHERE ... // match the parent primary key with the child foreign key

ORDER BY value DESC

and in the parameters you 'separator' would be the comma+space instead of the carriage return:

"% " ; ", " ) // this places the percent between value and name and the space between all the "rows" returned.

I may have to test to see the exact calculation.

beverly

• ###### 2. Re: Calculating fields and sorting

Hi Beverly,

I am having trouble getting the Execute SQL working... Would you mind checking my sample file?

• ###### 3. Re: Calculating fields and sorting

Try

ExecuteSQL ( "

SELECT \"Value\", \"Name\"

FROM Child

WHERE \"_fkParentID\" = ?

ORDER BY \"Value\" DESC

" ; "% " ; ", " ; Parent::__ParentID

)

Please read the entry in the Help system (and possibly other sources) about using ExecuteSQL() in FileMaker if you plan on using that function frequently.

This query language is quite powerful, but can get complex and finicky (names with blanks, reserved words – e.g. “Value” – injecting arguments, numbers vs. text etc. etc. … )

• ###### 4. Re: Calculating fields and sorting

Erolst - That worked!

Could you elaborate this command if pulling the "Names" from another table?

I tried playing with the values, but couldn't get it right and was hoping for some beginners luck!

• ###### 5. Re: Calculating fields and sorting

Try

ExecuteSQL ( "

SELECT \"Value\", N.\"Name\"

FROM Child C

JOIN \"Names\" N ON

N.\"__NameID\" = C.\"__fknameID\"

WHERE \"_fkParentID\" = ?

ORDER BY \"Value\" DESC

" ; "% " ; ", " ; Parent::__ParentID

)

• ###### 6. Re: Calculating fields and sorting

Thank you kindly!

• ###### 7. Re: Calculating fields and sorting

and possibly other sources

such as:

https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

Get the PDF and sample files.