Getting sort order of Portal as a number back to a different Table

[Slater]

Hi Everyone,

Bit of a head scratcher. I've got records (Table 2) in a portal on the page of Table 1) and that is sorted. The order of those records can be shown using a field Order_Number, set as a calculation of Get (RecordNumber). However  it all starts to go wrong when I want to use Order_Numer in a Table 3.  Order_Number always comes up as 1 in that Table 3 ... I'm presuming that for each record in the Table 3, it's in 1st place, as it's the only record that is being seen - so they all have the same first place, ie Get (RecordNumber) is always 1.

I want to sort the Table 3 in the same way as the portal is (the sort in the portal is very complicated, and so i can't just export that calculation to the Table 3).

I've tried unique relationships, putting the calculation as text and a few other things, but it always comes out as 1.

To fix it, I can only think of having a Loop in Table 2, goto first record, enter LoopNumberRecord as 1, and add through to it with each loop, so that I've got a hard coded figure for each record in Table 2, and then go to Table 3 and sort using LoopNumberRecord. But this isn't as simple as just being able to use Order_Numer from Table 2 in Table 3, because I'd like to call it up without going back to the Table 1, finding the relevant records in Table 2, and doing the Loop calculation. Is there any other way?

Hope the above makes sense!!

Cheers

[Slater]

Hi All, so writing all that above must have helped me! I've gone with the Loop method, but looping on the portal row rather than going into the different Table etc, works quite well, and hopefully won't be too time costly with large portals! If there is a better way though, do let me know

[comment]

What is the relationship between Table 3 and the other 2 tables?

P.S. Please use meaningful names for your tables, so that we can understand the overall situation. I am particularly puzzled by the fact that you want to sort records in one table in the same order as records in another table. That would make sense only if the two tables contain the same records - which would then raise the question why do you have two tables with the same records.

 

[Slater]

Thanks Comment, so kind of you to reply. I've got it working, probably a cludge, but I did want to reply to your post with the information you asked for.

So the Tables and Relationships are as follows:

Table 1 - Events

Table 2 - Activities

Table 3 - People

The portal is within the Events window, and it is a list of Activities. The portals also have some limited info brought over from the People table.

They're linked together with the following relationships:

Events & Activities: joined by ID_Event

Activities & People: joined by ID_People

The portal within the Events window shows Activities information that they did at that Event. I can sort the portal via things like which Activities they did, how many times, if they gave +ve feedback about the event etc. And I can highlight certain rows of the portal, ie if they gave the event more than 5 out of 10.

But then I want to bring look at all the highlighted portal row's People in a different window, the People window, which is easy enough. And then I'd like to sort those people in that window, so that they're in the same order as they are in the portal.

Their portal row number I'd got via Activities Get (RecordNumber), and that showed correctly in the portal. But when I looked at the People window, that same field was always 1. The same filed would show the correct portal row when in the portal, but 1 when in the People window.

I sorted it in the end by having a field called Portal Row Number, looping through the portal rows, and just increasing each portal row by x+1. Because that was just a simple number, it was easily viewable in the People window. I was sure that there'd be a more simple way to get that portal number number across, so that was the question.

Thanks though Comment, all the best

[comment]

35 minutes ago, Slater said:

But then I want to bring look at all the highlighted portal row's People in a different window, the People window, which is easy enough.

I am not sure how exactly you do that. And I also couldn't follow the rest of your description. Get (RecordNumber) in a portal shows the portal row number; I don't see what could be gained by collecting these values in a loop - it will always be just a sequence of 1, 2, 3 ...  

I could understand if you collected the related ID_People values into a global field/variable. Then you could use a calculation field in the People table to determine the index of the person's ID in the global and sort by that.

Alternatively you could define a global gEventID field in the People table and use it to define a relationship to another occurrence of Activities, using both event ID and people ID as match fields. Then populate the global with ID of the specific event you want and sort the records by the related value/s in the new TO.

 

 

 

[Slater]

Thanks, that second approach was the basis for my first attempt. I've got it working now, thanks so much again