Compare Records

[torch_55]

I have an Excel file that is updated weekly. I have to import the records into FileMaker and would like to compare the records each week to see which ones have a new key field since the last set of records was issued last week. I can compare them in FileMaker, Excel or input into another format, such as CSV, but some how would like to highlight the records that are new and have been added since last  week.

 

Thanks in advance for your help.

Rick

[doughemi]

Create a Creation_Date field which auto-enters the creation date of the new records of the imported data, if you don't already have one.  Create an unstored calculation field NewData =

 

Let(

LastFriday = GetAsDate(7 * Int ((Get(CurrentDate)+7) / 7 )-9);

If(Creation_Date > LastFriday, 1)

)

 

This calculation will return a 1 if the creation date is later than last Friday.  You can then set conditional formatting using the formula NewData = 1 to create your highlight.

[torch_55]

doughemi,

 

Thanks for the reply. I guess that is another reason to put those house keeping fields in every database. Even if you don't think you need them, they windup helping solve problems.

 

I have one build on the issue... There is a requirement to update just certain fields if the key field already exists in the database and to add all fields if it is a new record that doesn't already exist. From reading other posts I think that the import option to update matching records in found set with the add remaining data as new records checked will work. I will use your method to mark the new records and then go back and import the other fields that are needed for these records.

 

Any other suggestions or cautions regarding the data import will be greatly appreciated. Thanks again for your help.

 

Rick

[doughemi]

Why don't you import the data to a utility table? Then you can use a relationship to determine if the key field already exists in your main table and set a flag.  A script can then loop through the utility table and update the current records, and also add the new ones in one operation. 

[torch_55]

Doughemi, Not sure what you mean by a utility table. Is it a temporary table or something else. If you could point me in the right direction for more info on this and the scripts that you mentioned, that would be great. Thanks, again - Rick

[doughemi]

I meant a table which is used exclusively for importing records. It would have all the fields required to accept the imported data, plus an auto-entered serial number id field, plus a global number field g.newRecordKey, plus the autoentered Creation_Date field previously mentioned.

 

It would have a relationship to your main table utility::key = main::key.  

 

To create new records in main, you will need to create a new TO of your main table named MainNewRecord and relate it to utility by utility::g.newRecordKey = MainNewRecord::id.  Make sure that the relationship is set to allow creation of records in MainNewRecord.  

 

In general terms, the script would look something like

Go to Layout["Utility Table Layout"]
Go to Record/Request/Page[first]
Loop
   If[Count(main::key)>0  //There is already a record with the key value
      Set Field[main::YourFirstChangedField; utility::CorrespondingField]
      # add Set Field steps required to update existing record here
   Else
      # add a new record in main
      Set Field[utility::g.newRecordKey; ""]
      Set Field[MainNewRecord::key; utility::key] //see explanation below
      # now populate it
      Set Field[NewMainRecord::YourFirstField; utility::CorrespondingField]
      # add Set Field steps required to transfer new record here
      Go to Record/Request/Page[next; exit after last]
   End If
   ​Commit Records/Requests
End Loop
Delete All Records // in the utility table.

This is based on a technique described by Ray Cologon in his Filemaker Pro 10 Bible book. The Set Field step with an empty primary key in the parent table (g.newRecordKey) creates a new record and sets the primary key to the id field of the new record in the child table.  This happens only if the relationship allows creation of records.

 

[torch_55]

Dough emu, Thanks for taking the time to explain. I have the reference book you suggested and will find the example. Also the script that you included will be a great help. I am more and more impressed with FileMaker as I gain more experience I am even more amazed at the subject matter experts, like you, that are so willing to help others out. FM and the whole community are a really great experience. Thanks, again Rick

[doughemi]

 will find the example. 

Pg 423

[torch_55]

I meant a table which is used exclusively for importing records. It would have all the fields required to accept the imported data, plus an auto-entered serial number id field, plus a global number field g.newRecordKey, plus the autoentered Creation_Date field previously mentioned. It would have a relationship to your main table utility::key = main::key. To create new records in main, you will need to create a new TO of your main table named MainNewRecord and relate it to utility by utility::g.newRecordKey = MainNewRecord::id. Make sure that the relationship is set to allow creation of records in MainNewRecord. In general terms, the script would look something like Go to Layout["Utility Table Layout"] Go to Record/Request/Page[first] Loop If[Count(main::key)>0 //There is already a record with the key value Set Field[main::YourFirstChangedField; utility::CorrespondingField] # add Set Field steps required to update existing record here Else # add a new record in main Set Field[utility::g.newRecordKey; ""] Set Field[MainNewRecord::key; utility::key] //see explanation below # now populate it Set Field[NewMainRecord::YourFirstField; utility::CorrespondingField] # add Set Field steps required to transfer new record here Go to Record/Request/Page[next; exit after last] End If ​Commit Records/Requests End Loop Delete All Records // in the utility table. This is based on a technique described by Ray Cologon in his Filemaker Pro 10 Bible book. The Set Field step with an empty primary key in the parent table (g.newRecordKey) creates a new record and sets the primary key to the id field of the new record in the child table. This happens only if the relationship allows creation of records.

I meant a table which is used exclusively for importing records. It would have all the fields required to accept the imported data, plus an auto-entered serial number id field, plus a global number field g.newRecordKey, plus the autoentered Creation_Date field previously mentioned. It would have a relationship to your main table utility::key = main::key. To create new records in main, you will need to create a new TO of your main table named MainNewRecord and relate it to utility by utility::g.newRecordKey = MainNewRecord::id. Make sure that the relationship is set to allow creation of records in MainNewRecord. In general terms, the script would look something like Go to Layout["Utility Table Layout"] Go to Record/Request/Page[first] Loop If[Count(main::key)>0 //There is already a record with the key value Set Field[main::YourFirstChangedField; utility::CorrespondingField] # add Set Field steps required to update existing record here Else # add a new record in main Set Field[utility::g.newRecordKey; ""] Set Field[MainNewRecord::key; utility::key] //see explanation below # now populate it Set Field[NewMainRecord::YourFirstField; utility::CorrespondingField] # add Set Field steps required to transfer new record here Go to Record/Request/Page[next; exit after last] End If ​Commit Records/Requests End Loop Delete All Records // in the utility table. This is based on a technique described by Ray Cologon in his Filemaker Pro 10 Bible book. The Set Field step with an empty primary key in the parent table (g.newRecordKey) creates a new record and sets the primary key to the id field of the new record in the child table. This happens only if the relationship allows creation of records.