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

Recommended Posts

CJ Finn

I have scripted an import of data that has records with KEY, CODE, REF, BATCH, and DATE fields. The BATCH and DATE fields work together form a serial number of sorts for each record; an actual serial number for each record could be created if needed without much effort. I need to compare subsequent import batches to determine if:

 

 

  • there are new records in the newest batch that did not exist in the preceding batch;
  • there are records that existed in the older batch that are not included in the newer batch; and,
  • whether a record common to both batches has any changes (redline between new/old fields).

 

All records from all batches are maintained in a single table. I don't really care when the records remain the same between the batches, but I do need to see the variations to ensure that there aren't any errors. Errors can lead to fines you see.

 

I'd like to have a single layout with two or three portals, each one showing one type of change (omitted, new, modified).

 

I expect that I will need a few self joins to make this work. This becomes confusing to me quickly however.

 

Self-joining based on the KEY field with a second condition where the BATCH fields do not match would seem to be the way to go. Would I want or need to use some sort of global field to specify which batches I wish to compare? Further, what is the mechanism to omit common, unchanged records?

 

Any pointers you might be able to share would be appreciated.

 

CJ

Share this post


Link to post
Share on other sites
Maarten Witberg

Wouldn't the standard import feature where you update existing records based on a matching field do exactly what you need to do? Or do you really need to be able to identify each new batch in full?

Share this post


Link to post
Share on other sites
CJ Finn

Hi Maarten:

I have a need to review the new, omitted, or modified records as these records are all related to legal requirements that my company must follow. Further, having just completed a manual review in which I compared thousands of such records to print copies of the same regulations, I found that there were more than a few typo's and irregularities in the imported data. Accordingly, I need to manually verify why a record was omitted, added, or modified between batches to ensure that it wasn't simply an error.

CJ

Share this post


Link to post
Share on other sites
AHunter3

I would...

 

• Import the new records in a "holding tank" table, which would have equivalent fields to the final-destination table;

 

• Then run a script that loops through the newly-imported holding-tank records; you can make the script "smart" about how and where it writes over existing matching records in the real table. The relationship between then can be set up to allow for the creation of new records on the real-table side of the relationship.

 

• You can keep the holding-tank tables with a comment field that lets you annotate what you did or did not do with each one; and a "batch" code field (or set of fields) that says it was imported from this filepath at this time

Share this post


Link to post
Share on other sites
Maarten Witberg
Import the new records in a "holding tank" table, which would have equivalent fields to the final-destination table;

 

I like that idea. You can take it one step further. Still match them up by the ID of the actual table. Also add a PersistentOne (calc = 1 ) field on the left hand side and a 0/1 option on the right hand (holding table). Now you are able to keep tabs on the history for each individual 'real' record, and using the PersistentOne = 1 as an extra match field in a second TO, point to the holding record that has the currently approved / validated info in it.

One more step and you store the content in the 'holding' table and only the keys in the 'real' table.

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