Portal sorting refresh problem

[clmoss]

I have 3 db's. The first has a 'Priority' field where numbers 1 to 10 can be inserted. The second db has a calculated result which is just the value of the first db's priority field. The third db has a portal with a relationship with the second db with a sort by 'Priority' so it will list jobs with Priority 1 at the top of the portal, then 2, then 3 etc. The problem is when I alter the priority in the first db although it is changing the priority number in the portal it is not refreshing the list in order of 1,2,3 etc. unless I close the db or go to a different layout and go back again.

Thanks

[Kurt Knippel]

Portals do not magically refresh. Something must happen for them to do so. Switching layouts is one of the most consistant ways to cause a portal to refresh. This is the price of dynamic queries.

[RSGC]

captkurt said:

Portals do not magically refresh. Something must happen for them to do so. Switching layouts is one of the most consistant ways to cause a portal to refresh. This is the price of dynamic queries.

I have a similar situation. In my case the field the portal is set to sort on is a calculated numeric field. The user has the choice to select one of two sort orders by clicking a button which runs a script that changes a global variable in the portal table. This alters the value in the "sorted" field. The sorted field is defined:

nDivision.Sort.Value=IF(gDivision.Sort.Order="Circuit",nCircuit.Points,nLloyd.Points)

The calculation works, and so does the script (it changes the value in gDivision.Sort.Order appropriately, and also the nDivision.Sort.Value field updates in the displayed portal immediately). However, the actual order of the rows does not follow the values in n.Division.Sort.Value. In other words the Portal does not resort. It does NOT resort when I change to a different layout and then change back. The only thing that I have seen that forced the portal to refresh the sort order so far is to redefine the nDivision.Sort.Value field (I am hoping for other ideas, obviously). Closing the file and reopening also works, but that is also not my idea of a good solution. I have tried switching to Find view mode, Preview view mode, A different layout and a different view mode, all with no success so far.

Any suggestions welcome. I am running FMP 7 Developer 7.0v1.

Thanks!

Bob

[RSGC]

I found a not so pretty work around: I changed the nDisplay.Sort.Value from a calculation to a number field. I wrote a script that

1) changes to the portal table

2) finds the affected records

3) does a <Replace Field Contents> script step

4) Goes back to the original layout

and the portal is resorted. Very cool that it is working. Kind of kludgy compared the calculated field idea (Note: I stole that idea form another post here somewhere- I am not trying to take credit for the idea). If there is a beter way I would like to know. The databases and found sets are small enough that this is a speedy enough answer for my needs at the moment.

Bob

[cellmaker]

RSGC -

I am having exactly the same problem (I was happy to see somebody doing the same thing I was).

I figure the root problem is that the calculation field is not indexable because of its dependency on a global, but it does seem odd that the sort obviously can be done, since opening and closing Define Fields forces the portal resort.

For the moment, this method seems to be a write-off. I've searched around the site for other portal-sorting methods, but I can't seem to find anything. Can anyone point us in the right direction on a workable alternative?

Thanks.

[RalphL]

cellmaker

You didn't say anything about global fields in your previous post on this subject. Globals can be used as the primary key. They don't work as the foreign key. Calculations can be used on both sides of a relationship. You can not use a calculated foreign key if you want to allow creation of related records.

In your problem you said you were using calculated ones as the keys on both sides. This would not require global fields.

[cellmaker]

Ralph -

Much confusion. Were you responding to me or someone else?

My portal relationship doesn't use globals. However, the sort I was trying was just like RSGC's < nDivision.Sort.Value=IF(gDivision.Sort.Order="Circuit",nCircuit.Points,nLloyd.Points) >, which uses a global as the basis of a calculation. The result is what would be sorted.

Unfortunately, the result of this calculation cannot be indexed, so this seems to be the problem with using it for the sort criterion.

Nevertheless, it's odd that FM will go ahead and perform the sort once, when you close Define Fields. If it can do it once, why can't it do it other times?

Anyway, I was trying to think of some clever way to have the sort based on something that can change but which is not dependent on a global or an index-disabling relationship. Or some other method.

As RSGC concluded, I could do a a replace on the sort field for the related records, but this seems inelegant and a poor method in a multi-user solution because records might be locked by other users. (Or maybe this is no big deal -- it's just a sort, after all.)

Ideas?

[RalphL]

I don't think the global is the problem. The sort is working but the portal is not refreshing. Try setting the key field to its original value, i.e., set it to itself. See if that refreshes the portal.

[cellmaker]

Eureka (!)

It seems that modifying any non-global field in the to-be-sorted file will cause a refresh. Setting a field to to its own value even works. The field you modify does not have to be present in the portal.

So adding

Set Field[PortalFile:Anynonglobal; PortalFile:Anynonglobal]

Set Field[]

works like magic.

Another day, another knot unbound.

[RalphL]

I am glad it worked but it should work without the extra step. I will have more to say about a problem I had with portal refreshing when I get it all working.

[johnrh]

I just want to say A VERY BIG THANKS to everyone who contributed to this thread. It is exactly the probelm I have been wrestling with - and getting VERY frsutrated with for a couple of days.

So thanks Guys

JohnRH