Swapping content of two records

[Ralph Schwegler]

I am looking for the most efficient / fast running way to swap the content of two records (first / last, choosen in a script if swap needed).

The idea behind this is allowing the sorting in the portal view to be disabled, as it might dramatically increase the speed.

In the attached file all records with the same "ID_Offert" belong to the same offer, and will appear in a portal in "Offer".

My idea would be:

• store the data from the last record in variables,

• write the data from the first record in the last record

• write the data in the variables in the first record

It will work in a server envirornment.

Thank you for your advice!

Resort.fp7.zip

[Ralph Schwegler]

As I got no hint on this issue, I searched myself and will implement a "cocktail sort" in my example. If anyone is interested, I can post it here.

Cheers

[Ralph Schwegler]

Here it is.

The red colored number is the sorting as the user whishes it. The green colored number is the row as Filemaker will show in a portal without any sorting (and thus makes it faster). Hope you can understand it.

Search for any "ID_Offert" and change the values in the red colored number. Let the script run.

Method:

•push the hares (high numbers) down, then

•pull the turtles (low numbers) up, then

•go ahead doing that until there is no change

Thank you for any comment on that

Resort.fp7.zip

[comment]

I asked myself why the idea of swapping data - ALL data! - among two records gave me the shivers. I came up with:

1. There's a point during the procedure when both records have the same data, and the origin record's data is held in variables only. A failure at this point will result in permanent loss of data.

True, if the procedure is performed in a portal, it can be reverted as long as the parent record remains uncommitted. However, I am not sure how exactly one would test the success of the procedure - and anyway such test would be irrelevant in case of a power failure.

2. Any tracking of 'last modified' becomes fairly useless, and so does sorting records by creation order.

I also cannot help wondering about what is the actual problem being solved here:

The idea behind this is allowing the sorting in the portal view to be disabled, as it might dramatically increase the speed.

Records in a portal are always sorted - if not by the portal, then by the underlying relationship. To change a record's placement in the sort order, it would be quite sufficient to modify the record's "rank" field - see:

http://fmforums.com/...281#entry285281

And how many records do you have in a portal anyway, that sorting can "dramatically increase the speed"?

Lastly, but most importantly:

None of these methods can work in a multi-user environment - unless you actually want a swap performed by one user to apply to all other users.

[Vaughan]

Hey Ralph, it sounds as though you've found something really fun and interesting to do in FMP. Don't let people like Comment -- who point out that it's neither necessary nor good practice and most probably won't work in a real production environment -- put you off having a good time!

Dull and boring idea: identify a real problem that a client is having then build a solution that will save them time and money.

[No I'm not in a grumpy mood today. OK maybe just a bit.]

[Ralph Schwegler]

(CUT)

1. There's a point during the procedure when both records have the same data, and the origin record's data is held in variables only. A failure at this point will result in permanent loss of data.

True, if the procedure is performed in a portal, it can be reverted as long as the parent record remains uncommitted. However, I am not sure how exactly one would test the success of the procedure - and anyway such test would be irrelevant in case of a power failure.

2. Any tracking of 'last modified' becomes fairly useless, and so does sorting records by creation order.

I also cannot help wondering about what is the actual problem being solved here:

Records in a portal are always sorted - if not by the portal, then by the underlying relationship. To change a record's placement in the sort order, it would be quite sufficient to modify the record's "rank" field - see:

http://fmforums.com/...281#entry285281

And how many records do you have in a portal anyway, that sorting can "dramatically increase the speed"?

Lastly, but most importantly:

None of these methods can work in a multi-user environment - unless you actually want a swap performed by one user to apply to all other users.

Thank you comment for your constructive …comment!

regarding 1.:

you're right. There is no data in there which is mission critical, it can be reconstructed if needed. But: one workaround would be to write that data in a recovery table, do the swap, and finally delete the recovery record.

Is committing the parent record needed to take over the changes?

regarding 2.:

There is no creation/modify timestamp or name needed (in my case).

regarding your unnumbered considerations:

a ) the users partly get in the database via vpn (in my case), though it needs to be as slick as possible.

b ) it is multi-user (in my case) and the changes have to be valid for all users

c ) the sorting done by the portal is enough, I don't want to add another condition (like the manual sorting field) as this acts as a brake

d ) we have 2 to about 40 lines in the portal

Have a nice day and thank you

[comment]

Is committing the parent record needed to take over the changes?

If the changes are performed from the parent's layout, then yes.

[bcooney]

Ralph. What is slow? The portal draw, the sort rank script? It is important to know. Because even if you have a swapping sort rank technique that is quick, perhaps it's the portal redraw that is slow.

Are you committing the records (children and parent) each time you edit a sort rank field? Maybe if you avoid this, then you can gain some performance.