1 Reply Latest reply on May 12, 2011 10:46 PM by aammondd

    Imported data to Portal Question

    CarlSowden

      Title

      Imported data to Portal Question

      Post

      Hello there,

      I'm doing ODBC imports from one database to File maker.

      I have one field importing from the 'other' database that contains several lines of important data (in the one text field),

      I.E

      FIELD=

      3000 X (UNIQUE_ITEM_CODE) (ITEM_DESCRIPTION)

      3000 X (UNIQUE_ITEM_CODE2) (ITEM_DESCRIPTION2)

      3000 X (UNIQUE_ITEM_CODE3) (ITEM_DESCRIPTION3)

      and so on.....

      This field is imported via a match field called JOB_NUMBER

      What i want to do is to be able to seperate these lines and put them into a portal as individual records all relating back to the JOB_NUMBER field.

      I there a way I could convert this text field containing (let's say 3) lines of data into a Portal that would beable to display these 3 lines of data as individual records?

      Any help would be amazing!

      Carl.

        • 1. Re: Imported data to Portal Question
          aammondd

          There are a number of ways it can be done. The portal part is the easy part once you have the new records created.

          You may have to do some script processing of the records to do the text manipulation in an automated fashion.

          If your record set doesnt contain too many you can manually do the splits via copy and paste and it would take less time.

          You would first have to create a new table with the fields Job Number, Sequence Number, Description (you dont need the sequence number but its helpful) You need to create a relationship between your first table and this new one based on Job Number. Allow the creation of records in the new table via the relationship.

          Then you can add a portal object to a layout showing individual records in the first table and put the description field from the new table in the portal. (you can add the sequence number if you want to populate it yourself or just make it an auto entry sequential number field.)

          You can then either run a script to cycle through the records doing text manuipulation to substring out the individual lines and adding them as new records in the portal field or simply copy and paste portions of the text into new portal rows creating the records that way. (Ive done it this way a few times for things because the script programing and text manipulation can be a bit tricky.)

          When you are done splicing the text you can remove the old text field from your layout. (You dont have to remove the field from the table just in case at some later point you might want to see the orginal data string but you might comment on the field in your data base design.

           

          the new table and relationship are needed regardless of the method above

          There is a way to do this with calculation fields as well but you wont really need the portal for that. Also it wont allow you to add new lines as individual records but can be easier to achieve the display you want without making new tables or using the portal tool. The text calculations to display the fields would probably be almost as complex as those needed to parse the text to make new record via a script so for future flexibility purposes Id still split  it into multiple records.