record merge

[patrick]

Hi all,

Filemaker Pro 5.0

Is there a way to merge records within the same database?

A (very) simplified example would be :

Number : Name : Sports : Music : Arts :

1 : Henri : T5 : : :

2 : Henri : : : M6 :

3 : Henri : : P8 : :

I am looking for something which would combine these 3 records into one without losing any data.

ie.

1 : Henri : T5 : P8 : M6 :

Thanks,

patrick

[Chuck]

quote:

---

Originally posted by patrick:

Hi all,

Filemaker Pro 5.0

Is there a way to merge records within the same database?

A (very) simplified example would be :

Number : Name : Sports : Music : Arts :

1 : Henri : T5 : : :

2 : Henri : : : M6 :

3 : Henri : : P8 : :

I am looking for something which would combine these 3 records into one without losing any data.

ie.

1 : Henri : T5 : P8 : M6 :

Thanks,

patrick

---

Set up a self join relationship based on the field that you want the merge to be based on. In this case it would be the name field. Place a portal of that relationship on a layout, and script a loop through the portal, setting fields from related records to fields in your current record. If you allow the deletion of records in the portal, then once you've gotten the information you can delete the related records (since you have the data in the current record) and move on the the next record in a loop.

Just to make sure that it's all working right, since the final script would delete records, I would make sure to have a backup of the database.

The script would look like this:

Go to Record/Request/Page [ First ]

Loop

Go to Portal Row [ SelfJoin ]

Loop

If [ not IsEmpty ( SelfJoin::Sports ) ]

Set Field [ Sports, SelfJoin::Sports ]

End If

If [ not IsEmpty ( SelfJoin::Music ) ]

Set Field [ Music, SelfJoin::Music ]

End If

If [ not isEmpty ( SelfJoin::Arts ) ]

Set Field [ Arts, SelfJoin::Arts ]

End If

Go to Portal Row [ Next, Exit after last ]

End Loop

Go to Record/Request/Page [ Next, Exit after last ]

End Loop

This assumes that only one of the records will have information you want to keep for each individual field.

[patrick]

Thanks to whoever posted a reply to my call for help yesterday. I am having problems writing my script (especially the IF statements) with what was given to me in the script example, but I'll give it another bash today. If all fails, I may ask again for help.

Thanks for the help anyhow,

Patrick

[patrick]

Hi Chuck,

Thanks for your post yesterday. I am still having problems with scripting inside FM.

Could you possibly shed some light of what is going on here with the If statement?

1/

In the Script Definition window I see this:

if ["not IsEmpty(indoRelShip::Games1)"] instead of, as you have it in your example :

if [not IsEmpty(indoRelShip::Games1)]

Why is the program adding quotes to my code?

All I type in the Specify Calculation window is :

not IsEmpty(indoRelShip::Games1)

where indoRelShip is my SelfJoin and Games1 the first field I am searching through

2/

[Games1 , indoRelShip::Games1]

I can't get the program to accept the line above in the Specify Calculation window.

I want to write:

Set Field [ Games1 , indoRelShip::Games1] and it keeps rejecting this with claims of too many operators??

Is the scripting case-sensitive?

Wish I had more flexibility in the script window. ie wish I could write the code myself not rely on a function menu to clickon. Very frustrating.

I am a programmer by the way.

Thanks again for any help,

Patrick

quote:

---

Originally posted by Chuck:

Set up a self join relationship based on the field that you want the merge to be based on. In this case it would be the name field. Place a portal of that relationship on a layout, and script a loop through the portal, setting fields from related records to fields in your current record. If you allow the deletion of records in the portal, then once you've gotten the information you can delete the related records (since you have the data in the current record) and move on the the next record in a loop.

Just to make sure that it's all working right, since the final script would delete records, I would make sure to have a backup of the database.

The script would look like this:

Go to Record/Request/Page [ First ]

Loop

Go to Portal Row [ SelfJoin ]

Loop

If [ not IsEmpty ( SelfJoin::Sports ) ]

Set Field [ Sports, SelfJoin::Sports ]

End If

If [ not IsEmpty ( SelfJoin::Music ) ]

Set Field [ Music, SelfJoin::Music ]

End If

If [ not isEmpty ( SelfJoin::Arts ) ]

Set Field [ Arts, SelfJoin::Arts ]

End If

Go to Portal Row [ Next, Exit after last ]

End Loop

Go to Record/Request/Page [ Next, Exit after last ]

End Loop

This assumes that only one of the records will have information you want to keep for each individual field.

---

[ben_matthews]

quote:

---

Originally posted by patrick:

Why is the program adding quotes to my code?

---

Well, because it does. I don't really know why, but it adds the quotes when you are looking at the script in scriptmaker. If you were to click "specify" again, you'd see your formula without quotes.

quote:

---

[Games1 , indoRelShip::Games1]

I can't get the program to accept the line above in the Specify Calculation window.

I want to write:

Set Field [ Games1 , indoRelShip::Games1] and it keeps rejecting this with claims of too many operators??

---

When you add the "Set Field" command (from the menu on the left), click on "Field" and select Games1, and then click on "Specify" to type indoRelShip::Games1.

quote:

---

Is the scripting case-sensitive?

---

Nope.

quote:

---

Wish I had more flexibility in the script window. ie wish I could write the code myself not rely on a function menu to clickon. Very frustrating.

---

Hmmm...nice feature request, although I doubt I would use it except for very simple scripts...

-ben

[patrick]

Guys, thanks for your help.

I can now enter the script.

However my problem is this : I have 192 fields to search. Which means that I have to enter that If statement 192 times with appropriate modifications like below:

If["not IsValid(indoRelShip::Games1)"]

Set Field["Games1","indoRelShip::Games1"]

End If

If["not IsValid(indoRelShip::Games2)"]

Set Field["Games2","indoRelShip::Games2"]

End If

etc... 192 times

Is it possible within that script window to have an array and then use that array to pass a param to a repeat loop? That's how I would do it normally:

FieldList = (Games1, Games2, etc...)

Repeat with i = 1 to FieldList.count

thisField = Field.count

If["not IsValid(indoRelShip::thisField )"]

Set Field["thisField ","indoRelShip::thisField "]

End If

end repeat

That would save heaps of time and space.

Once again, thanks for any suggestions.

[patrick]

Oops, my repeat loop should read (2nd line):

Repeat with i = 1 to FieldList.count

thisField = FieldList

If["not IsValid(indoRelShip::thisField )"]

Set Field["thisField ","indoRelShip::thisField "]

End If

end repeat

So much for rushing...

[Chuck]

quote:

---

Originally posted by patrick:

Guys, thanks for your help.

I can now enter the script.

However my problem is this : I have 192 fields to search. Which means that I have to enter that If statement 192 times with appropriate modifications like below:

If["not IsValid(indoRelShip::Games1)"]

Set Field["Games1","indoRelShip::Games1"]

End If

If["not IsValid(indoRelShip::Games2)"]

Set Field["Games2","indoRelShip::Games2"]

End If

etc... 192 times

Is it possible within that script window to have an array and then use that array to pass a param to a repeat loop? That's how I would do it normally:

FieldList = (Games1, Games2, etc...)

Repeat with i = 1 to FieldList.count

thisField = Field.count

If["not IsValid(indoRelShip::thisField )"]

Set Field["thisField ","indoRelShip::thisField "]

End If

end repeat

That would save heaps of time and space.

Once again, thanks for any suggestions.

---

What platform are you working on? If you're on the Mac, you can do this with AppleScript (I just wrote an article on how to do so, it should be in the next issue of FileMaker Magazine).

If you're on Windows, there's no way I know to get around all 192 sets of if statements. Perhaps someone else here will know how.

If this is a one time operation, and you're working on Windows, then perhaps it would be worth your time to take the solution to a Mac, use AppleScript to perform the operation, and then take it back to the Windows platform.

If you're unfamiliar with AppleScript, or you don't have access to a Mac, or you just don't want to tackle it yourself, let me know. I would be able to do it for you if you like, for about an hour's work.

Chuck

[Chuck]

I think Ben probably answered your questions, but since I was the one who wasn't clear, here's my response.

Ben's right in that the ScriptMaker just adds the quotes to the calculation. It will do this whenever you enter a calculation for a script step. When you get the Set Field script step working, you'll notice that the second part (what you're setting the field to) will have the quotes around it.

Regarding the Set Field command, I was writting the scripts as you would see them (almost) when you see the entire script. Set Field takes two parameters: the field to set and what to set it to. These two parameters are set separately. Specify the field with the button that says "Specify Field". Tell ScriptMaker what to set it to by clicking the "Specify" button. So double-click the Set Field script step, click "Specify Field" and find your "Games1" field and double-click it. Then click "Specify" and enter the related field "indoRelShip::Games1".

Regarding the ability to enter scripts by typing them in, it has been a long requested feature of FileMaker by developers. It won't be much help, but I think there's a program out there which allows this to a limited extent, as well as the copying of scripts from one file to another. If I find out what the software is, I'll post the information here.

[patrick]

Chuck,

Thanks for the offer however the environment is Windows only.

quote:

---

If you're on Windows, there's no way I know to get around all 192 sets of if statements. Perhaps someone else here will know how.

If you're unfamiliar with AppleScript, or you don't have access to a Mac, or you just don't want to tackle it yourself, let me know. I would be able to do it for you if you like, for about an hour's work.

Chuck

---

[Chuck]

quote:

---

Originally posted by patrick:

Chuck,

Thanks for the offer however the environment is Windows only.

---

But is it an operation that you only need to do once? I mean, if you take care of it this time, is it ever going to need to be cleaned up again? If not, then the problem could be solved that once on a Mac using AppleScript to make the code easier, and once the data is cleaned, taken back to Windows.

Chuck

[This message has been edited by Chuck (edited September 28, 2000).]