Jump to content

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


Recommended Posts

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

Screen Shot 2021-04-15 at 3.52.49 PM.png

Edited by WF7A
Grammar police
Link to post
Share on other sites

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?

 

Link to post
Share on other sites

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.

Screen Shot 2021-04-15 at 4.23.42 PM.png

Link to post
Share on other sites
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?

 

Link to post
Share on other sites
Posted (edited)

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

Screen Shot 2021-04-15 at 4.33.59 PM.png

Edited by WF7A
Link to post
Share on other sites

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.

 

Link to post
Share on other sites
Posted (edited)

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 by WF7A
Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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

Link to post
Share on other sites
Posted (edited)

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 by WF7A
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.