Converting from Repeating fields to Portal

[kevybaby]

Okay, I know this should be simple, but I am missing something. I searched the web (which brought me to this site) and searched this site and cannot get a resolution.

 

I am trying to convert data in a few repeating fields (basically, contact info for each company) into Portal data. I followed the steps in this FileMaker guide multiple times, even creating an unneeded serial number (I was planning on just using the Company field as the match field). However, I cannot seem to get through the import step: under "Target" at the top of the dialog box, the newly created table ("Contact Names") is grayed out, no matter what setting I have. I have tried it with both with the relationship set up and disabled. Is there something really obvious I am missing?

 

Maybe I should just leave them as repeating fields?

 

The summary of the steps I did is:

1. Create new table
2. Create new destination fields
3. Starting with a layout with the old fields, export to a FMP file (which I name "TRANSFER") with just the Company (or Serial) field and the repeating fields I am trying to split.
4. Switch to a layout with the table and Match field (this shouldn't make a difference)
5. Attempt to import records (and I can never get past the field matching dialog box).

 

FMP 9.0v3

Windows XP Pro

Not sure what else I can provide

 

 

Screen Shot.bmp

[Wim Decorte]

before you start the import, go to a layout based on that Contact table and then do the import.

[kevybaby]

I had done that, but I took my third fresh look at the situation and figured out the missing detail: I had not set the correct "Show Records From" in the Layout Setup (Layout Mode > Layouts > Layout Setup). Once I set that, I was able to easily import. Now just to tweak a couple other things and I should be golden!

[LaRetta]

Hi levy baby,

"even creating an unneeded serial number (I was planning on just using the Company field as the match field). "

Oh but serial number is VERY necessary since they are used in binding relationships. Using names is problematic because the first time someone's name is misspelled and a related record is created then their name corrected, you will break the relationship. Or when a female gets married and changes their name etc.

If you add an auto-enter serial (start at one, increment by one) and then import and check 'perform auto-enter' during import when asked then you'll be all set. :-)

[brian rich]

Hi kevybaby

 

I am working on a number of legacy FMP databases where repeating fields are used all over the place, and am steadily converting the repeating fields to related tables. Trying to export and import was a pain because of the way that repeating fields are exported - you have to process the export file afterwards to get the merge fields separated out so you can bring them back individually into the contact file.  Irritating and fiddly to do and get working.

 

The quickest way I have found to achieve this is to create the records directly in the related file using the 'magic key' technique and a loop construct to go through the related records.  I guess you already have a relationship (say Contact) beween your company and contact file.  This might be achieved by having a key field called _ks_CompanyUID in the Contact table which is linked to the __kp_CompanyUID field, the primary key in the Company table.

 

With the magic key technique you also have a second relationship which is only used for creating the contact records.  To make this work, in your contacts file add a numeric field called (say) _ks_ContactUID.  This should be set to autoincrement by serial number, so each time a record is created in the Contact table, it has a unique serial number,  In your Company file, create a gobal numeric field called (say) _gmagicKey - it can be any name you wish.  In the relationship diagram, create a second occurrence of the contact table called (say) contact_Create.  Now link _gmagicKey in the Company table to _ks_ContactUID in the Contact table.  In the dialog box that appears,  check the allow creation of records via this relationship on the contact table side.

 

The magic bit is that if you set _gmagicKey empty, then set the _ks_CompanyUID field in the Contact table to the value of the Company ID through the Contact_Create relationship, the contact record is created immediately and is now linked via the main Contact relationship. You can now continue setting the other fields in the newly created contact in the same way.  The script would look something like this:

 

 

                            #  Context is a layout based on the Company table

                            #  Set up magic key

                            Set Field [ COMPANY::_gMagicKey; "" ]

                            #  Set up primary link between company and contact

                            Set Field [ CONTACT_CREATE::_ks_CompanyUID;COMPANY::__kp_CompanyUID ]

                            #  Set up contact details
                            Set Field [ CONTACT_CREATE::First Name; COMPANY::CONTACT_FIRST[1] ]
                            Set Field [ CONTACT_CREATE::Last Name; COMPANY::CONTACT_LAST[1] ]
 

Those four steps will be sufficient to create the contact record

 

Now to build a record for each repetition, just put a looping construct around that with an incrementing loop counter with enough values in it to cover every reptition.

 

If your contact name fields have 20 repetitions

 

                    Set Variable [ $count; Value:20 ]
                    Set Variable [ $loop; Value:1 ]
                    Loop

                             #  Set up magic key

                            Set Field [ COMPANY::_gMagicKey; "" ]

                            #  Set up primary link between company and contact

                            Set Field [ CONTACT_CREATE::_ks_CompanyUID;COMPANY::__kp_CompanyUID ]

                            #  Set up contact details
                            Set Field [ CONTACT_CREATE::First Name; COMPANY::CONTACT_FIRST[$loop] ]
                            Set Field [ CONTACT_CREATE::Last Name; COMPANY::CONTACT_LAST[$loop] ]         

                    Exit Loop If [ Let($loop = $loop + 1;$loop > $count) ]
                    End Loop
                    Commit Records/Requests

 

Now you have a record for each repetition.  You'll probably need an if statement in there so you can skip the blank repetitions and not write blank records. 

 

You can now set up an outer loop that works steps through each company record and then runs this loop to build the contact records for each company.

 

You can find out more about the magic Key technique here:  http://www.filemakerhacks.com/?p=2364

 

Enjoy

 

 

Brian

[kevybaby]

Hi kevy baby,

"even creating an unneeded serial number (I was planning on just using the Company field as the match field). "

Oh but serial number is VERY necessary since they are used in binding relationships. Using names is problematic because the first time someone's name is misspelled and a related record is created then their name corrected, you will break the relationship. Or when a female gets married and changes their name etc.

Oh, good point. After I did the move, I was wondering how I was going to handle company name changes (since that is the driving force). I will change it to use a serial#. Thanks for the tip!

 

Hi kevybaby

 

I am working on a number of legacy FMP databases where repeating fields are used all over the place, and am steadily converting the repeating fields to related tables. Trying to export and import was a pain because of the way that repeating fields are exported - you have to process the export file afterwards to get the merge fields separated out so you can bring them back individually into the contact file.  Irritating and fiddly to do and get working.

 

I read your post a couple of times and I am curious what you find so difficult about the FMP provided process (I am not trying to start a flame war: I am curious if there is something I missed). Once I found the missing (simple) detail from my initial effort, I found the process quite simple:

 

 

1. Create new table
2. Create new destination fields
3. Create new layout with the Portal fields showing with the correct "Show Records" set.
4. Starting with a layout with the repeating fields, export to a FMP file with just the  Serial field and the repeating fields.
5. Switch to a layout with the table and Match field
6. Import records, choosing the correct options in the dialog boxes (New Records, Split Repeating Fields).

Is there something I am missing? And like I said, not trying to start a fight, I just want to learn something new :-)

 

Kevin

[brian rich]

Hi Kevybaby

 

I guess my approach is because our databases are littered with repeating fields, so I have had to go through the process many times. 

 

For me, using Filemakers export/import process is all or nothing when it comes to repeating fields. If your repeating field has 20 repetitions, the import process gives you 20 records, even if only a few have data in them.  You have to process the import afterwards to get rid of the empty data records.

 

The other problem is that - in several of our databases -  the repetitions had being used to build a data grid and the position of the data in the grid was meaningful to the users, so my new related records needed to show which repetition they had been in. With the export/import method I could only manage that with post-processing the data, so at the beginnning  I ended up writing looping scripts to do it anyway.

 

To me, the whole advantage of scripting the transfer to create records directly is that I can control which repetitions are created in the related table, I can add additional data to the related records which may not be explicit in the original data (such as the position in the repetition, or a full name field which is a concatenation of First and Last names with a space between), and  I can clean the data during the transfer process (get rid of excess spaces and carriage returns for example).  The other 'useful' aspect of scripting the process like this is that it makes it repeatable - I often find my first pass doesn't do everything I need - so I tweak the script and repeat the process.

 

If you only need to do it once, then it may be enough to use the export/import method.  I don't think you have missed anything.

 

Regards

 

 

Brian

[bruceR]

"If your repeating field has 20 repetitions, the import process gives you 20 records, even if only a few have data in them.  You have to process the import afterwards to get rid of the empty data records."

 

No, that is not the case. It has never been the case. Though writing a script to process any empty records would be pretty trivial. If set up correctly, FileMaker only imports the filled repeats. See attached simple example.

ImportRepeats.fp7.zip

[kevybaby]

Hi Kevybaby

 

I guess my approach is because our databases are littered with repeating fields, so I have had to go through the process many times. 

 

For me, using Filemaker's export/import process is all or nothing when it comes to repeating fields. If your repeating field has 20 repetitions, the import process gives you 20 records, even if only a few have data in them.  You have to process the import afterwards to get rid of the empty data records. 

 

 

Hmmm... I did not run into this. The fields I was transferring had 15 repetitions available but it only transferred a row of repetitions that had info and regardless of where the info was. For example, the data I was transferring was comprised of six fields of contact info (Prefix, FName, LName, Title, PPhone, email). There were a couple of records where I only had info in the Title field (people who had left for example) and when I made the transfer, it transferred that stand-alone title into the proper place. If I only had five contacts, it only created five records on the re-import.

 

Like I said, once I set the correct "Show Records From" in the Layout Setup, the linked FMP instructions were spot on and very easy.