Sorting text in a field

[Rigsby]

I have 25 separate fields containing different numbers and text values, but these are limited to 1 word per field: E.G:

Txt1 =

[Søren Dyhr]

You would need 3 fields first a recordID then one that gathers all the pilcrow delimited values not visible to the user, then have a unstored visible calc'field that uses then related dynamic valuelist from the relation RecordID to RecordID. This makes you get a sorted list in the unstored one via ValueListItems(

--sd

[Rigsby]

:-) I was just working on the value list idea! Thanks for the quick reply. The problem I'm having is that the ValueList field is not updating unless I change layouts or enter the field. However, I think I can get round that!

[Søren Dyhr]

You need to stuff it inside a Evaluate( and remember to make before each " ....

--sd

[-Queue-]

Even with Evaluate, the screen may not refresh unless you use a script to set the text field to itself.

[transpower]

If you had Version 7 Developer, you could use the Bubble Sort custom function developed by Lane; see http://www.briandunning.com/filemaker-custom-functions/detail.php?fn_id=36.

[Søren Dyhr]

Even with Evaluate, the screen may not refresh unless you use a script to set the text field to itself.

Unfortunately it's so, but the requirement was sans script, so only thing left is the recursive function Transpower mention!

--sd

[Rigsby]

Maybe I'm stupid, but what exactly do you mean by

"You need to stuff it inside a Evaluate( and remember to make before each " ...."

[Søren Dyhr]

Please forget it's 7.0 features, however - I think you're barking up the wrong tree!!! I do actually regret giving answer to this thread.... But lets put this a little in perspective, why is it so urgent to make a fields value as little as possible under script control??

By and large is Filemaker the only database application on the marked to sit between two chairs by being a database tool with spread sheet 'ish behaviors. This is probably done to make users migrate from spread sheets seamlessly when tasks require a little more sorting and searching than say possible in excel. The number of post in forums like this, on repeating fields stems probably from this migration as well.

The other reason for requests like this and for plugins executing scripts on departure from fields, stems from lack of coherent user interface rules and guidance for the Windows and Linux platforms. Opposed to this rules Mac OS out the use of unforgiving actions lacking a rollback feature... I can see why developers in vanity seeks to accomplish a lot behind the scenes like John Lennon says: "Life is what happens to you while you're busy making other plans." never the less is it no-no on user interfaces deployed on Mac's - because if all tools behaves similar you can keep the guesswork to an absolute minimum despite being new to the user.

The other issue Apple raises is who's in control - the machine or the user. Here goes a saying joke: "Why haven't artificial intelligence caught on yet?? There will always be missing a .dll file!!!" ...it's obvious behavior in a supermarket not to pick the tin/can in the bottom of the pile except with the arsonists mind, what is the joy in fooling the user into something irretrievable???

Now what is it about database designs where calc'fields and repeating fields becomes illegitimate children, well as long as we can accept wasting storage on redundant information not entirely coherent or reliable in flat file structures ...could nobody really care. But just think of address changes, in my neck of the woods do people hardly live more that 7 years at the same place, so if you don't store data in a separate table can't you just search for the random invoice to the person you like to send an email announcing a new product you wish to sell - they might very well live elsewhere than say the address of the first ever invoice. But what is it about relational design of databases that bothers some?? It's the size of the task to maintain data in the freshest manner, here does relational designs seek to have data actually living in just one place where it then can be referenced if you need an appearance.

I this thread do you seek to sort the values in a field, but First Normal Form dictates "one value per field" why isn't the values spread out as individual values in individual fields in individual records ....so they can be sort and unsorted???

Return delimited list and repeating fields have only relevance when used as relational keys, where operators like "less than", "more than" "different from", "equal" or the "cartesian" exhibit a slight inconvenience in handling a relationship even if they're stacked. Then of course there are container fields that can't be sorted in any way human intelligible, feel free - they're alright to stuff in repeaters.

Please tell me why a sorted portal is wrong in your case, and the number of fields you're gathering from why are labes just labels why not type definers in a related table??? My advice should have been - wrong file structure!!

--sd

[Rigsby]

It is sometimes difficult to ask for help, and at the same time explain the sense of the full solution on is working on! I try to keep posts short and to the point so that those who might be able to help don

[Fenton]

Another idea which I've used in the past is to try and hit the "possible" duplicates with more than just a test on the name. That is to also use such things as the 1st part of the address (there are often differences and typos in the end of addresses, but the 1st bits are usually the same), the phone number (if any), an email (if any).

A company which gets hits on more than one of the relationships, with the ID that it matches the same in all cases, is almost certainly a duplicate.

[Rigsby]

Thanks fort he Tipp,

I

[comment]

Don't know if this might help - it's a bit crude, but perhaps it will give you some fresh ideas.

test.zip

[Søren Dyhr]

Let face it Filemaker is a tool, not artifical intelligence ...thinking a little further on this topic do I think Ray have a point with these templates here:

http://www.nightwing.com.au/FileMaker/demos/FindSimilar.sit

http://www.nightwing.com.au/FileMaker/demos/FindSimilar.zip

Which is in .fp5 format BTW.

It's probably a better way than what you apparently do at present ...I'm guessing here but isn't it something like this???

http://www.fmforums.com/threads/showflat...rt=all&vc=1

--sd

[Søren Dyhr]

Don't know if this might help - it's a bit crude, but perhaps it will give you some fresh ideas.

Very fine indeed, no freshing issues what so ever ...I can hardly wait until -Queue- gets to this thread and throw his huge leftbrain capacity at it, and makes it exploiting all the bells and whistels of 7...

The coolest part of it is that it can act as key on both sides of the relationship, only part missing is that each word when converted to a horizontal string instead of pilcrowed is some Clair V... (how is it spelled??)

--sd

[Rigsby]

"Crude" Yes, but it works..... And you're right it just gave me a very fresh idea that I think might work. I need to get this tested on my 500,000 records for speed, but I think you just gave me a push in the right direction.

Thanks, and thanks again

Rigsby

[Rigsby]

Coool!!! just what I needed! I've up-ed it to 25 field (words) and it works fine. Now I need to test it on the full 500,000 records.

Thanks

[comment]

Good - let's hope it will save a tree or two...

On reflection, I think I will take back the word "crude". I am now convinced that with n fields, n-1 comparisons per field is the minimum required to compute the index addresses. IMHO this is as streamlined as it will ever be.

[Rigsby]

I streamlined it a bit by using letters instead of numbers for the Choose(Position( part, as it had problems with more than 10 words otherwise. But this was easy to do with a substitute("26","Z") etc. command. This way I can sort up to 26 values without having to play with the position.... -1 command. 26 is more than enough for my needs, but I'm sure there would be a way to get more.

Very streamline, very effective... I'm still facing lots of other problems, but this has really helped.

[comment]

I didn't go over 10 in the example because I wanted the method to be easy to understand.

You can bring it up to any number by using a fixed-length code with a delimiting character. For example, to accommodate up to 1000 values, the initial address calculations could be:

Right("000" & **original calculation** & "*", 4)

Then in the final calc, you would have:

Choose(

Int( Position( indexT, "333*", 1, 1) - 1) / 4,

text1, text2, text3,...

I have a feeling this may a tad faster than Substitute(), but I don't have 500,000 records to test it...