Re: Compare tables for duplicates

Pillguy,
I am still mostly on FMP6 which had roughly the same concepts since
version3. So for me there was only one table per file so a data base was
multifiles. Now, with FM7-8, while it can be multifiles also, any file
may have many tables. So, again for me, table = file.
Then my previous advice is basically based on habits on FMP6. But the
concept still works in FM7-8, while the solution, I am sure could be
done more elegantly.
Back to your question, a few words on basics. There are regular fields
that have a different value in each record, and global fields which have
the same value in all the records of that file (table). A normal field
can either have a type like 'Texte', 'Date', or so, or other types one
of them being 'Calculation'. If I create a regular field that I name
'Constant' of type 'Texte' and that I make 'Calculated', then I may say
that that calculation will be 1 and type 'Number'. So in all records I
have a regular field that has the value 1 in each record.
It was heavily used in FMP6 to create relationships between FileA and
FileB based on a 'Constant' field in both files, which allowed to
transfer global data from A to B.
Say in A, I want to tell B of the value of the NameID of the current
record of A. I then make a script line in A saying 'Set Field(gn1,
NameID)' where 'gn1' is a global field numeric, then a second script
line passes the hand to B by 'Perform Script (external on FileB, 'Name
of Script in B'). In B now, that script start with SetField( gn1,
relationship via1::gn1), and I have got in B in its own gn1 the proper
NameID that could used be to Find that record for instance, or else.
In FM7-8, there are variables that do roughly what the globals did in
FMP6; but with more flexibility.
Back to your question (really this time), you create a relationship
between A and B based on say 'YrField' (the one you want to find the
duplicates).
Now, in A you create a regular text field named 'Duplicate' that will
be made by calculation equal to : Case(Sum(relationship via
YrField::constant) >1 , "X",""). It will put an 'X' in 'Duplicate'
automatically only for the reords where the number of matches of YrField
exceeds 1. Isn't it what we call duplicates ? It is the result you want.
Now, It took me 20 lines or more to explain my first 3 lines.
If it is still not clear for you I can make a little example and send it
to you privately if your address is a valid one.
Please advise.
Remi-Noel



"Pillguy" <jchpharmd@xxxxxxxxx> a écrit dans le message de news:
2005123014200043658-jchpharmd@xxxxxxxxxxx
> Remi-Noel,
>
> Sorry, I am new to Filemaker. Lots of SQL experience, but this seems
> to be a bit different.
>
> Are you saying that I should create a new field in table A (lets say I
> call it 'Field6'.
>
> Then I lost you on the (number, calc)=1. Not sure what that is. I
> see the data types, but not sure how to apply.
>
> Your next statement looks like a script of somesort?
>
> Sorry, really. Handholding please!
>
> Thanks
>
> pillguy
>
>
>
>
> On 2005-12-30 08:50:24 -0600, "Remi-Noel Menegaux"
> <rnmenegaux@xxxxxxx> said:
>
>> You make a relationship in A between A and B based on 'YrField'.
>> Suppose in A you have a field 'Constant'(number, calc) = 1.
>> In A you do 'Duplicate'(txt)(calc) = Case(Sum(Relationship::constant)
>> >1, "X", "").
>> Remi-Noel
>>
>>
>> "pillguy" <jchpharmd@xxxxxxxxx> a écrit dans le message de news:
>> 2005123008151450073-jchpharmd@xxxxxxxxxxx
>>> I have two tables, both have a field with the same data type and
>>> length (11 digit numbers).
>>>
>>> I want to compare the two tables.
>>>
>>> Table A is my master table. Table B is the one I want to compare A
>>> against.
>>>
>>> I created a field in Table A called Duplicate. Basically, I want a
>>> comparison of B against A, and when there is a match (duplicate), It
>>> will populate the Duplicate field with something (1, Yes, etc).
>>>
>>> Thanks in advance.
>
>
>
>


.

Relevant Pages

  • Re: Compare tables for duplicates
    ... There are regular fields that have a different value in each record, and global fields which have the same value in all the records of that file. ... If I create a regular field that I name 'Constant' of type 'Texte' and that I make 'Calculated', then I may say that that calculation will be 1 and type 'Number'. ... Say in A, I want to tell B of the value of the NameID of the current record of A. I then make a script line in A saying 'Set Field' where 'gn1' is a global field numeric, then a second script line passes the hand to B by 'Perform Script. ... I created a field in Table A called Duplicate. ...
    (comp.databases.filemaker)
  • Re: Compare tables for duplicates
    ... There are regular fields that have a different value in each record, and global fields which have the same value in all the records of that file. ... If I create a regular field that I name 'Constant' of type 'Texte' and that I make 'Calculated', then I may say that that calculation will be 1 and type 'Number'. ... Say in A, I want to tell B of the value of the NameID of the current record of A. I then make a script line in A saying 'Set Field' where 'gn1' is a global field numeric, then a second script line passes the hand to B by 'Perform Script. ... I created a field in Table A called Duplicate. ...
    (comp.databases.filemaker)
  • Re: Seperating CSV rows into new, seperate files
    ... of a script but it still has one more problem. ... will import the list will ignore duplicate rows. ... >> and I am working on a way to parse a CSV file of class lists from MS ... The difference is that the session number changes. ...
    (comp.lang.python)
  • Add User to group through comparison to other users memberships
    ... I have a "New User" script which is very complex and handles adding most ... AD properties upon creation, address tab, phones, profile tab, ... strUsr2Duplicate = inputbox("What is the username you wish to duplicate ... msgbox "No user requested - No group memberships will be duplicated" ...
    (microsoft.public.windows.server.active_directory)
  • Re: Please can some help me with this script
    ... Other than that the script ran fine. ... I assume you use a dictionary object to avoid duplicate names, ... objDictionary.Add strNextLine, i ...
    (microsoft.public.windows.server.scripting)