Set Variable, go to, check duplicates and set field...

[jamesjames]

Hi. New here, nice to meet you all. Ive got a small problem you might be able to help with.

I need to move data from one related table to another (to keep the old data if we change the original data). And I have made a script that works fine. But I would like to add a if "Protokoll register::ProtPunktID FK" exist within the related results (Protokoll::Prot ID) skip "Set field" steps.

Should I use a go to find mode and perform find, or what is the smartest way? How do I then make it search on a variable like $Protokoll for example?

Set Variable [ $Protokoll; Value:Protokoll::Prot ID ]

Go to Related Record [ From table: “Protokoll register”; Using layout: “Rapport protokoll register” (Protokoll register) ] [ Show only related records ]

Go to Record/Request/Page [ First ]

Freeze Window

Loop

Set Variable [ $Namn; Value:Protokoll register::Namn ]

Set Variable [ $ProtpunktID; Value:Protokoll register::ProtPunktID ]

Set Variable [ $Gruppering; Value:Protokoll register::Protokoll Grupp ]

Go to Layout [ “Protokoll Line” (Protokoll Line) ]

// Here I would like to check if it exist any record with "Prot ID=$Protokoll" and "Protokoll register=ProtPunktID" in this table. Then I can just skip the next steps with "if" command.

New Record/Request

Set Field [ Protokoll Line::ProtPunktID FK; $ProtpunktID ]

Set Field [ Protokoll Line::Namn; $Namn ]

Set Field [ Protokoll Line::Protokoll Grupp; $Gruppering ]

Set Field [ Protokoll Line::Prot IDfk; $Protokoll ]

Commit Records/Requests [ Skip data entry validation; No dialog ]

Go to Layout [ “Rapport protokoll register” (Protokoll register) ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

Go to Layout [ “Protokoll” (Protokoll) ]

Many thanks for any tips.

[comment]

If I understand this correctly, importing the records would be more suitable. You can set the ID field in the target table to validate as Unique, Validate always. This will prevent importing duplicate records.

[jamesjames]

Aha. Ive never tried that. But that does not take in count what records are related right? I only want to copy certain specific records that are related to the first table. And I also need it to work on the web so I figure this is the only way...

[comment]

If the file is open, only the found set in the source table will be imported. IOW, do a find, then import. I think this should work in IWP too, but I haven't tested it.

[jamesjames]

Thanks, but that still leaves me with the first problem. if I run the script two times, it still create duplicates containing the same data.

I want it to check the records, and only import those ones that is missing/not represented in the table (hence the first question)... And then make the record unique thou the related "protIDfk" field.

Any more tips?

[comment]

Perhaps I don't understand the context here. If you define some field to validate as Unique, Validate always. then Filemaker will not allow importing a record with a value that already exists in the table.

[jamesjames]

Does the unique value only validate within in the current find? Otherwise its no good because there is no uniqe value to work with, only a combination of two field values that together must be unique.

Oh, now when im writing this it hits me that I could make a calc field, combine two fields and use that as a unique value. Ill give that a go. thanks.

[comment]

You cannot validate a calculation field - but you could use a field with auto-entered calculated result instead.

[jamesjames]

Hi again.

After some testing I found that the import works brilliant. Many thanks for the tips. I just go to related, and then import. The validation with auto-entered calculated result is perfect.

again, many thanks...