Re: Compare tables for duplicates

Thanks for all of your help.  It is greatly appreciated.



On 2005-12-31 01:36:14 -0600, "Remi-Noel Menegaux" <rnmenegaux@xxxxxxx> said:

It is not what I wrote. In your formula, you are trying to add (SUM) all related NDC, while the relationship is on NDC. It can't work.
You have to write :
Case (Sum (FormularyMaster::CONSTANT) > 1;"True";"")
Then it will work, as you'll be counting ( ie adding the number of times you find a MATCHING constant). You really need the field Constant = 1.
That said, the method given by FP seems more appropriate to FM7-8.
Remi-Noel

"Pillguy" <jchpharmd@xxxxxxxxx> a écrit dans le message de news: 200512302159078930-jchpharmd@xxxxxxxxxxx 
Remi,

I created the Field in my table A. It is of type text, with the calculation as such:

Case (Sum (FormularyMaster::NDC) > 1;"True";"")

Where FormularyMaster is the name of table B, and NDC is the matching field.

I am not getting any data populated in the Field in table A. It is called "InFormulary"
Do I need to run something or do I have the syntax incorrect?

TIA


On 2005-12-30 18:09:33 -0600, "Remi-Noel Menegaux" <rnmenegaux@xxxxxxx> said:

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
    ... related NDC, while the relationship is on NDC. ... >> fields that have a different value in each record, and global fields ... >> other types one of them being 'Calculation'. ... >> record of A. I then make a script line in A saying 'Set Field(gn1, ...
    (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: Compare tables for duplicates
    ... Then my previous advice is basically based on habits on FMP6. ... that that calculation will be 1 and type 'Number'. ... NameID)' where 'gn1' is a global field numeric, then a second script ... in A you create a regular text field named 'Duplicate' that will ...
    (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)