Sort timestamps (from child table) in parent table's portal

[Rich S]

Greets, all:

Since I can't insert a portal within a portal, I'm stuck: I have a parent (EVENT table) - child (DATE_TIME table) relationship where I want to list (the calculated) multiple timestamps in the Upcoming Events column. I used the List function but it apparently doesn't sort timestamps; I also used the SortValues calculation but didn't get far with that, either. More than likely I'm bollocksing-up the calculation (or using the wrong approach altogether) so I'd appreciate some assistance.

TIA for your help!

Rich

Edited April 15, 2021 by WF7A
Grammar police

[comment]

This is rather confusing. You say you have a parent-child relationship, but you want to list multiple values in a portal row? I am assuming the portal is to the child table - so where are those multiple values coming from? And if they are coming over a relationship, why can't you simply sort the relationship so that the List() function returns a list that is already sorted?

In any case,

SortValues ( List ( SomeTable::TimestampField ) ; 5 )

should return a sorted list of timestamp values. If you're seeing a different result, then perhaps they are not timestamp values?

 

[Rich S]

Thanks, your calc worked. : )

 

The "multiple values" reside in the child table; the portal shown below "feeds" the child table's separate Date, Time From and Time To fields; I use a calc (in the child table) to create a timestamp.

 

One problem was that the user could add dates/times that preceded existing ones in the portal, so that threw the sort order off in my poorly constructed List calc.

[comment]

2 minutes ago, WF7A said:

The "multiple values" reside in the child table

I still don't get it. Is your portal to the child table? Or is it perhaps a master-detail layout of the parent table?

 

[Rich S]

It's to the child table: from EVENT_PARENT to DATE_TIME ... BUT the portal is a self-join of EVENT_PARENT.

Edited April 15, 2021 by WF7A

[comment]

Well, it's a mystery to me. I think that each child record should have only one value. You said:

43 minutes ago, WF7A said:

I can't insert a portal within a portal

That would make sense only if there is a grandchild table.

 

[Rich S]

Maybe I need some coffee: What I meant to say is that yes, each child record contains only one timestamp, but there are multiple fields to create that one timestamp as shown above. I'm sorry.

 

The "portal-in-a-portal" reference was that it's easy to sort records in a portal (based on "x" value), so that's just one way of doing it. As an aside, I did happen upon a portal-in-a-portal article that improvises on that idea,( https://filemakerlovers-org.fmlovers.org/filemaker-portal-in-a-portal-4d-printing/ ) but there wasn't a viable link to the sample file.

Edited April 15, 2021 by WF7A

[comment]

11 minutes ago, WF7A said:

BUT the portal is a self-join of EVENT_PARENT.

Well, then it is NOT a portal  to the child table. And I presume the calculation field is in the parent table? So why can't you sort the relationship to the child table by the timestamp field? As I said, then the List() function would return a result that is already sorted - and you would eliminate the extra processing of converting each value in the returned list back to timestamp for the purposes of sorting.

 

[Rich S]

Thanks again--I'll give it a shot in the morning. My brain is full.

[Rich S]

Sorry for the delay--I was out of town.

Yes, there were two TO's--parent-child EVENT_PARENT to DATE_TIME, and EVENT_PARENT to EVENT_PARENT_xEventFilter; the former I used for data entry and the latter for displaying events to users. However, after additional requests from the users for more types of lists/reports, I bagged the idea of using a self-join portal and just went with layouts using List mode--it's not nearly as compact or elegant, but it gets the job done a whole lot easier.

Edited April 20, 2021 by WF7A