Change sort order based on related field's calculation

[21st Century Man]

Hi, all:

I have a two-part problem:

First, in the attached file, is there a way (in the Parent table/layout) to create a calculation (in CombinedLevels__lct) that will show a comma-delimited list in ascending order--similar to what you see in the portal--sorted by the numeric equivalent grade levels (CHILD::Data__lxn) instead of CHILD::Data__lxt? I can't use a portal since it eats up too much space in the layout I'm working in.

Second, I tried all kinds of permutations in the Custom Function to populate CHILD::Data__lxn with a zero (for the value, K, in Data__lxt) but I just can't push a zero in the field--I have to change the field type to a Number field and manually type it in instead of having a calculation/Custom Function to do the work. What does FileMaker Pro have against calculated zeroes?

Have a day...

Rich

 

CustomOrder--Modded.fmp12

Edited Thursday at 08:29 PM by 21st Century Man

[comment]

Files still cannot be posted directly:

 

25 minutes ago, 21st Century Man said:

show a comma-delimited list in ascending order

I am assuming you want a list of values from one field, sorted by another field in the same record? If you sort the relationship by the "other" field, then the List() function will get you the list in the wanted order, and you only need to substitute the carriage return with a comma. There may be other options, but this one seems the simplest.

I am afraid I couldn't understand the 2nd part.

 

Edited Thursday at 08:53 PM by comment

[21st Century Man]

I uploaded the file to Wormhole: https://wormhole.app/PpYMZA#-v9Zw3p8WE5pTLUKQUG-CQ

I tried List(), but as far as I can tell that only works on one field--I want to sort the values in Data__lxt by Data__lxn. (It'll make more sense when you see the file.)

As for the second part...just a simple example: Say you use SetField ( blivet ; 0 ) -- the field, blivet, won't be populated with a zero.

[comment]

Here is an implementation of my earlier suggestion:
https://wormhole.app/nA6jNl#p0Dylwpd2GPzcM0tyHGmoA
 

But wouldn't it be much simpler to do it like this?
https://wormhole.app/4xd5jy#PhNt3RTEroF16XeXeysjGA

 

42 minutes ago, 21st Century Man said:

Say you use SetField ( blivet ; 0 ) -- the field, blivet, won't be populated with a zero.

I cannot reproduce the problem. What type of field is "blivet"?

 

 

[21st Century Man]

Thanks for your time and effort, but I don't think I made myself clear. The grades (Data__lxt) need to appear in the same sequential order as their number equivalents, Data__lxn, so the (appearance) order should always be (in ascending order) Preschool > Pre-K > K > 1 > 2 > 3...  that's why their numbered equivalents are number line based -2 (Preschool) > -1 (Pre-K) > 0 (K) > 1 (1 ) > 2 (2) > 3 (3)... so a sort based on Data__lxn is how I want Data__lxt to appear.

As for the 0 (zero) not appearing, if you change Text__lxn to a calculation field, the calc, "GradeLevelConversion__CFpub ( Data__lxt )", then go to the Child table/layout, if K is in Data__lxt the corresponding Data_lxn field will be empty when there should be a zero. All the other fields display Data__lxn correctly, it's just the result is a zero, it doesn't. Why?

 

 

Edited Friday at 01:13 PM by 21st Century Man

[comment]

1 hour ago, 21st Century Man said:

the (appearance) order should always be (in ascending order) Preschool > Pre-K > K > 1 > 2 > 3...

Isn't that exactly what happens in both of the files I posted? In both the portal and the calculation field?

 

1 hour ago, 21st Century Man said:

if K is in Data__lxt the corresponding Data_lxn field will be empty when there should be a zero.

Well, that's very different from:

17 hours ago, 21st Century Man said:

Say you use SetField ( blivet ; 0 ) -- the field, blivet, won't be populated with a zero.

The problem here is quite simple: your custom function has an apostrophe after the "K" character here:

V = "K'"  ; 0 ;

 

[21st Century Man]

*smh* My wife has been after me for months about having cataract surgery done. I may have to acquiesce after this oversight.

I looked at both files--when I created a new record in Parent, the grade levels (text ones) weren't ordered at all. I'll take another look tonight when I return home.

MANY thanks again for your patience.

[comment]

30 minutes ago, 21st Century Man said:

when I created a new record in Parent, the grade levels (text ones) weren't ordered at all

It won't happen in the 1st file until you populate the Data__lxn field in the newly created child records.

It will happen in the 2nd file, but only after you commit the record (that's a good thing: you don't want portal records to fly up and down while you're still working on them).

 

Edited Friday at 05:27 PM by comment

[21st Century Man]

I still had trouble with the files, so I thought to myself, "Why am I trying to force alpha onto numeric instead of the other way around? So I did some tweaking and voilà, it works.

Thank again for your help!

https://wormhole.app/l3ny25#-pszd5EDrqsN_DADejvsMA

[comment]

Your link does not work for me.

I don't know what kind of trouble you had with my files, esp. the 2nd one that sorts by a value list. I don't think it could get any simpler than that.

Actually, if this is related to your previous thread then I believe it could be much simpler yet (as I also stated there):
https://wormhole.app/D1xpDL#hTFHublK65RlCVZYpy26kg

 

Edited 7 hours ago by comment

[21st Century Man]

Well, that's just strange--TeacherAssignments3 worked great, so I'll have to investigate further. : |