Importing from other db after validation

[Denniz]

Hi there!

Got a question.

I have 2 db's, one "Temp" database with an Import button to import data from a dbf file, after importing i'm activating the other db; "Orders".

Now i want to import the data from the "Temp db" in my "Order db"(script on startup), but i first want to check a calculation field(unique code) to see if its a new, editted or unchanged record.

If they are the same..do noting,

if there is a difference...change the record...else

...make a new record.

I also want a statusfield after updating (so if a record is NEW, CHANGED or NOT CHANGED since last synchronisation)

I really dont have a clue how to do this, can somebody help me with this one?

thanks..

[cjaeger]

You could use the record modification date to check whether the record has been modified. not in your case, though, because you are updating from a dbf file.

In this case, make a concenated field with all the relevant fileds:

name 1 &" " & name2 & " " & adress1 ...

Then do a comparison with the Exact() function in the tempdb using whatever key filed is apropriate.

field "select for import"(in temp db)

exact(confield, maindb:confield)

do a scripted find on selectforimport =1

delete found records

trigger a script in masin db to import (with update, your key field)

[Denniz]

Thanks, i'm on my way!

maybe a strange question but how can i get connected to the other database,

i'm now active in temp.fp5 en want to exact() in main.fp5 (field name = 'Key')

field "Key"(in temp db)

exact(Key, main.fp5:Key)

does not work.

You could use the record modification date to check whether the record has been modified. not in your case, though, because you are updating from a dbf file.

In this case, make a concenated field with all the relevant fileds:

name 1 &" " & name2 & " " & adress1 ...

Then do a comparison with the Exact() function in the tempdb using whatever key filed is apropriate.

field "select for import"(in temp db)

exact(confield, maindb:confield)

do a scripted find on selectforimport =1

delete found records

trigger a script in masin db to import (with update, your key field)

[Denniz]

Have it done know, forgot to make a relation

can you explain this part of your solution:

do a scripted find on selectforimport =1

delete found records

trigger a script in masin db to import (with update, your key field)

thanks.

Thanks, i'm on my way!

maybe a strange question but how can i get connected to the other database,

i'm now active in temp.fp5 en want to exact() in main.fp5 (field name = 'Key')

field "Key"(in temp db)

exact(Key, main.fp5:Key)

does not work.

[CobaltSky]

If I understand you correctly, the first part of what you want to do - the import - can be achieved using the third 'import action' option provided by FileMaker.

When you choose the import command, the resulting dialog offers radio buttons for a selection of action options at the lower right. The last of these is "Update matching records in found set" and is accompanied by a checkbox option to "add remaining records".

Once you have brought the records into your temp file, the 'update' import options should enable you to synchronise your current data with them.

In order to track the status of the data after synchronisation however, a couple of extra steps will be required.

I suggest that you create an additional text field called 'status' in your main file which is not present in the temp file. Then set up a script to import the records from the Temp file as follows:

Show All Records

Replace[No dialog, "Status", "Field1 & Field2 & Field3 & ...FieldN"]

Import [Restore, No dialog, "Temp.fp5"]

Show All Records

Replace[No dialog, "Status", "Case(IsEmpty(Status), "NEW", Status = Field1 & Field2 & Field3 & ...FieldN, "UNCHANGED", "CHANGED")"]

Exit Record/Request

Where the replace formulae 'Field1 & Field2 & Field3 & ...FieldN' are a concatenation of all the other fields on the record except the status field, and where the import options saved with the script are the "Update matching records in found set" and "add remaining records" options I referred to earlier.

With the above in place, your imports from Temp will be fully automated, and the text field 'Status' will indicate whether each record is 'new', 'changed' or 'unchanged' in the synchronisation process.

[Denniz]

you're understanding me very well and i'm coming close now but i miss some points

What do you mean with the 'synchronisation' , is it the part with "SetField[Exact(Keyfield;main::keyfield)]"? after that nothing happends to sync?

I'm now gonna try your solution, but think i miss some sync stuff in my temp file.

now my scriptmaker is as follows:

Delete all records [no dialog]

Import Records [restore, no dialog, "database.dbf")

SetField [Exact("Keyfield";"main::keyfield")]

Open ["main.fp5"]

Close[]

[Denniz]

Keyfield is mij unique field where i store most important info of that record (there is no ID). So i want to compare that Keyfield in my imported temp file with the Main database, if its equal, UNCHANGED, if the record CHANGED .., if the keyfield is NEW make a new record.

thats my whole story The script above only imports te record over and over again, no status text is seen in my status textbox..

[CobaltSky]

Hi Dennis,

Firstly let me say that I would never attempt a multi file database solution involving either relationships or synchronisation of multiple data sets without a permnanent unique ID field to identify each record.

If, as you say, your key field includes data that can be changed from one iteration to the next, then the whole process is unstable and the logic flawed - you could, at least in theory, end up with more than one record in Temp that matches a given record in the main file and vice versa. A unique ID per record is the only reliable way to track what is happening and to tie it down.

Secondly, the script I included in my previous posting works perfectly - I know because I tested it thoroughly before posting it. But I wouldn't recommend using it - or any other script, for that matter, without first introducing a unique record ID to both files.

[Denniz]

Hey Cobalt,

The Script works ALMOST perfect now

My Unique ID is the field i made by myself (Keyfield) so every record has his own ID/key. I have 2 more questions about my script.

when activating my first script in the Tempfile:

Delete all records [no dialog]

Import Records [restore, no dialog, "database.dbf")

SetField [Exact("Keyfield";"main::keyfield")]

Open ["main.fp5"]

Close[]

I get a message "this field is not midifiable" after OK the script goes on, how can i get rid of that message?

(when i delete "SetField [Exact("Keyfield";"main::keyfield")]" the warning is gone. But this one is quite important huh?)

Another question about your last script with the 'status' field, it works very nice......

BUT! When i import a NEW record it goes OK (it says NEW). When i do the same import again, the new record goes to CHANGED (it must be UNCHANGED). I Only want a CHANGED when something in a record (with the same ID/Key) is changed.

When this is working i'm a happy man

[CobaltSky]

Hi Denniz,

The Set Field expression you are using has no meaning, and that is likely why it is resulting in an error message.

The syntax for set field is:

Set Field ["Field you want to set", "what to set it to"]

However your script does nmot indicate which field is to be set, and the formula you have entered (using the Exact function) is a test rather than a result.

That aside, it is not clear to me what purpose the set field at that point in the process is likely to serve. If you are using the kind of process I outlined above, then a comparison of the incoming data with the previous contents of the main file is being conducted by the Replace [ ] script step which follows the import - and therefore need not also occur in the Temp file.

As regards the status field, if it is indicating that new data has changed on a second run of the import, then that seems to suggest that there is a difference between the concatenation formula you are using in the initial Replace [ ] step and the comparison formuls in the second Replace [ ], since that is the basis on which it is determined whether the data has changed.

[Denniz]

It was the comparison between the two replace[] scripts, they weren't the same...now they are and it's working!

Another *bonus* question is, is there a possibility to see 'what' is changed in the record? (maybe just by a * or show the old value?)

Hi Denniz,

The Set Field expression you are using has no meaning, and that is likely why it is resulting in an error message.

The syntax for set field is:

Set Field ["Field you want to set", "what to set it to"]

However your script does nmot indicate which field is to be set, and the formula you have entered (using the Exact function) is a test rather than a result.

That aside, it is not clear to me what purpose the set field at that point in the process is likely to serve. If you are using the kind of process I outlined above, then a comparison of the incoming data with the previous contents of the main file is being conducted by the Replace [ ] script step which follows the import - and therefore need not also occur in the Temp file.

As regards the status field, if it is indicating that new data has changed on a second run of the import, then that seems to suggest that there is a difference between the concatenation formula you are using in the initial Replace [ ] step and the comparison formuls in the second Replace [ ], since that is the basis on which it is determined whether the data has changed.

[CobaltSky]

There are a few ways to get a summary of what has changed - some of them quite a lot of work to implement. Howevr a relatively simple approach would be to insert delimiter characters (eg Pipes) between all the fields in the formulae for both the existing Replace [ ] steps, to create an additional field (called 'ChangeSummary') and then include an additional Replace [ ] step in the import script in the main file, immediately before the last Replace [ ] step that sets the Status field.

The additional Replace [ ] step would set the Change summary field, and would use a formula along the lines of:

Case(not Field1 = Left(Status, Position(Status, "|", 1, 1) - 1), "Field1: " & Left(Status, Position(Status, "|", 1, 1) - 1) & " >> " & Field1) &

Case(not Field2 = Middle(Status, Position(Status, "|", 1, 1) + 1, Position(Status, "|", 1, 2) - Position(Status, "|", 1, 1) - 1), "

[Denniz]

hey cool concept that ChangeSummary!

It works pretty fine BUT, now when i change something and import it, the CS field shows the same values as the recordset. So you can't see whats the old and what's the new value, it just shows all the fields.

Another flaw is that he just changes the first record with a difference, when i change more records then after importing he gives back just one record with a difference, so CS field stays empty in the other records (status field works fine btw)

Is it possible to just show the old value of the changed field in all changed records?

[CobaltSky]

If you use a formula along the lines I suggested, and place the additional replace step immediately before the replace that sets the status field, as I indicated, then the only fields which will be included in the change summary will be those that have changed. On records which have not chaged, the Change summary field will be empty.

If that is not the case, then I suggest that you have another look over my last post and compare it to your implementation to spot the difference.

[Denniz]

thanks CobaltSky, when you typed your reply i editted my post. I did exactly what you said BUT did nothing with the pipes maybe there i can find my error? (i now use FIELD1 & FIELD2 & FIELD4 etc)

[CobaltSky]

Yes, that might well be the problem. It definitely won't work without the pipes.

[Denniz]

ok but then i have to change it to; FIELD1 | FIELD2 | FIELD 3 etc.?

Then its gonna be a messed up field with some text and pipes

[CobaltSky]

True - it won't be pretty.

But that hardly matters. After all, we are talking about a temporary value written to the status field which will be placed there by a script step early in the import and overwritten by a later step in the same script. The user will never actually see it.

The *final* contents of the Status field, and ChangeSummary field - which the users *will* see - will be a lot prettier.

[Denniz]

It didnt't work, my status field sais 'changed' and the changeSummary sais; name: >> Peter |||| age: >> 23 ||||

And when i change 2 records, just 1 records work with the changeSummary so i think there is a flaw somewhere?

(FIELD1 | FIELD2 | FIELD3 didnt work, it has to be FIELD1 "|" FIELD2 "|" FIELD3 but that didnt seems the problem over here)

[Denniz]

I found one error, i had SET FIELD [ChangeSummary..

Now changed that to REPLACE CONTENTS [ChangeSummary..

but now when i import the database with one change, the Statusfield goes to "CHANGED" and the ChangeSummary shows all the recordinfo WITH all the pipes

[CobaltSky]

Actually neither FIELD1 | FIELD2 | FIELD3 nor FIELD1 "|" FIELD2 "|" FIELD3 will work. It has to be:

FIELD1 & "|" & FIELD2 & "|" & FIELD3 & ... etc

However that aside, the change summary field should not end up with any pipes in it, so if it is, I suggest that you go over your Replace Contents [ ] calc formulae again, as it sounds like there may be an error there.

[Denniz]

don't think there is something wrong with that script:

Replace Contents [No dialog, "ChangeSummary", ...

Case(not NumToText(FIELD1)= Left( status; Position( status; "|" ; 1; 1)-1); "FIELD1: "& Left( status; Position( status; "|" ; 1; 1)-1) & " >> " & NumToText(FIELD1)) &

Case(not NumToText(FIELD2)= Middle( status; Position( status; "|" ; 1; 1)+1; Position( status; "|" ; 1; 2) - Position( status; "|" ; 1; 1)-1);"

[CobaltSky]

In the portion of your code that you have included in your post I can see two problems. The first one was actually a problem in the syntax I posted for you. Sorry about that - it must have been getting late when I typed it out!

In the final line of the formula, two numbers are transposed. It should read:

...Middle( status; Position( status; "|"; 1; 2)+1; Position( status; "|" ; 1; 3)-Position( status; "|"; 1; 2)-1) & " >> " & FIELD3) & ...

The difference is the "; 2)-1) &" in place of the "; 1)-2) &". I imagine that you have this issue throughout the subsequent parsing expressions also.

Secondly, I note that you have included NumToText( ) expressions around the first couple of fields in the comparison formula for the change summary. That is fine, but in order to make it work reliably you will also have to put NumToText( ) around the corresponding fields in the formula for the second step of your script. So it should read:

NumToText(FIELD1) & "|" & NumToText(FIELD2) & "|" & FIELD3 & "|" ...etc

Last but not least, I couldn't tell from your post, but the formula for the first Replace Contents( ) will need to end with a pipe character after the last field.

Aside from the above, it is looking good. Try making the adjustments I've outlined and see how you go then?!

[Denniz]

That did the trick

It's working so perfect now! If you have another cool add-ons, let me know so i can make it total perfect

Thanks for helping!