Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
beju0506

Script or function to remove duplicate values from a return delimited field

Recommended Posts

beju0506

Hey everyone,

 

I know this is probably pretty simple, but Monday is getting to me and I can't seem to figure it out.

 

Do any of you know a way to remove duplicate values from a single return delimited field? I've got a field that is a concatenated list of values from various spots and I need to filter out duplicate values...

 

As always, your help is very much appreciated!

 

Thanks again!

 

-Justin

FM Novice and victim

Share this post


Link to post
Share on other sites
AHunter3

First, make a backup :)

 

 

then:

 

a) Create a calculation field of result type "number", call it RecID, defined as Get(RecordID). Create a new relationship of this table to itself based on RecID = RecID. This is a special selfjoin relationship that not only joins the table to itself, it explicitly joins this record and only this record to itself. Let's call that relationship "SelfJoin".

 

b) Create a value list defined as related values only of SelfJoin::YourField. (Starting from the native table occurrence of your layout, of course). Let's call that value list "MyFieldValues"

 

c) At this point, if you were to set the field itself to ValueListItems (Get(FileName), "MyFieldValues"), you'd end up with the existing values, still return-delimited, but without any duplicates, insofar as such is the nature of value lists.

 

d) You can therefore do the above in a looping script that loops through your records and sets each record's field in turn, going to next, exit after last, end loop.

 

e) Once done, you can nuke all of the above structural elements as you should have no further use for them, unless for some reason you will again acquire duplicates that you have to get rid of.

Share this post


Link to post
Share on other sites
beju0506

AHunter - Thanks for your reply!

 

I really would like to do it without creating a bunch of database relationships and structures... I need to be able to do it as a function or a script as the data that needs to be filtered exists for other purposes. Also, it wouldn't just be a one-time deal, it has to be done as needed.

 

So ideally, I'd have something where it would go to the first "value" in the field, store it somewhere, search the rest of the field for it, then if not found, iterate to the next, etc... creating a list of unique values that would at the end be put back into the field replacing the original data.... (I know, sounds really convoluted..)

 

I just don't really know enough about the text manipulation functions in FM to be able to do such a thing.

 

Any ideas?

 

Thanks again for your answer!

 

-Justin

Share this post


Link to post
Share on other sites
AHunter3

Well, yeah, actually that was a workable but clumsy way to go at it. Creating all the structure for temporary purposes & all.

 

OK, do this:

 

Set Variable [$ExistingValues, "¶" & YourTable::YourField & "¶"]

Set Variable [$Pos, 1]

Loop

.. Set Variable [$GetValue, Middle ($ExistingValues, Position ($ExistingValues, "¶", 1, $Pos)+1, Position ($ExistingValues, "¶", 1, $Pos + 1) - Position ($ExistingValues, "¶", 1, $Pos)-1)]

.. If [PatternCount ("¶"& $NewValues & "¶", "¶" & $GetValue & "¶") = 0]

....Set Variable [$NewValues, $NewValues & Left ("¶", Length ($NewValues)) & $GetValue]

..End If

..Set Variable [$Pos, $Pos + 1]

..Exit Loop If [GetAsNumber ($Pos) + 1 > PatternCount ($ExistingValues, "¶")]

End Loop

 

 

That oughta work and the only structural element you have to create is that script.

 

 

EDIT: Umm, sorry, once you've done that much, you then set the value of your field to $NewValues. Kinda forgot to mention that!

Share this post


Link to post
Share on other sites
beju0506

Ahunter,

 

Thanks again for your help!

 

I couldn't get the exact calculation to work, but I took a hint from your algorithm and worked something out that finally did work in the end! (I went by values instead of by characters when doing the manipulation of text)

 

Once again, I really appreciate it!

 

-Justin

Share this post


Link to post
Share on other sites
Romain

How about that one guys?

Set Variable [$ExistingValues, YourTable::YourField]

Set Variable [$Pos, 0]

Loop

.. Set Variable [$GetValue, Middle ($ExistingValues,$Pos+1,1)]

.. If [PatternCount ($NewValues , $NewValues & $GetValue)

....Set Variable [$NewValues,$NewValues & $GetValue]

..End If

..Set Variable [$Pos, $Pos + 1]

..Exit Loop If [$Pos + 1 = ValueCount ($ExistingValues)]

End Loop

Set Field [YourTable::YourNewField; $NewValues

 

Just seems simpler but you do have to create a YourNewField field.

What do you think?

Thanks for the thread anyway it was really useful and made my day after a few modifications of the script for my own use.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use