6 Replies Latest reply on Feb 28, 2013 10:11 AM by strngr12

    Import unknown number of notes and display with single record

    strngr12

      Hello Everyone,

       

      I have a FMP database where I am tracking assetts from a 3rd party database. Most of the data is contained in fields that have a one-to-one relationship to the assett being tracked - name, size, status, etc...

       

      However, the 3rd party database allows an indefinate number of users to make an indefinate number of notes on any particular assett. While these numbers are not large, there's no hard way of tracking this on a one to one basis as I can with say, assett size. There could be 3 users who each made five notes, there could be 6 users who each made two notes or there could be 4 users, one of whom made two notes, one who made three and two of whom made six notes.

       

      The database can export this info as a .csv with a column for the user and the note and some other infor about the note. Each note is a separate row so if Jow Schmo made three notes, there will be three separate rows each with the value Joe Schmo, the individual note, etc... Like so:

       

      name, note

      Joe, this is a note

      Joe, this is another note

      Joe, this is yet another note

       

      I'd like to creat a separate table for these notes that tie back to the individual record that the item resides in. For example:

       

      Table 1:

      field: Item 1

      field: size

      field: description

      ---> table 2:

      Note 1

      field: name

      field: note

      Note 2

      field: name

      field: note

       

      field: item 2

      field: size

      field: description

      --> table 2:

      Note 1

      field: name

      field: note

       

      Etc...

       

      Keeping in mind that there is no way to know before hand how many notes by how many users. This is why I would need an indeterminate amount of records in table 2 to be associated with a single record in table 1. Can anybody help?

       

      Thanks,

       

      Dan

        • 1. Re: Import unknown number of notes and display with single record
          keywords

          It seems to me that you need a separate Notes table, with each note as a single record. You can then set up a relationship to tie each note to its relevant record in Table 1.

          • 2. Re: Import unknown number of notes and display with single record
            strngr12

            Thanks, keywords.  That's what I'm figuring, but I don't know how to set up the relationship.  Do you know how I'd go about that?

             

            Thanks!

            • 3. Re: Import unknown number of notes and display with single record
              keywords

              I assume Table 1 is the assets you are tracking. Each asset record will have its own unique ID. In your notes table you will also need to have an asset ID field, into which you will insert the ID of the asset the noter refers to. Your relationship will match the asset ID fields in each table.

              • 4. Re: Import unknown number of notes and display with single record
                strngr12

                Thanks again, but let me run this by you to make sure I get it.  Let's say the unique ID is the name of the asset so in Table 1 I have a field "asset_name."  Each record in Table one will have a unique value in this field because the records are one-to-one records of each asset.

                 

                In Table 2 I also have a field called "asset_name."  In Table 2, however, there are as many records with a given value in that field as there are notes on the asset in question.  For example:

                 

                Table 1:

                     record 1:

                          asset_name: valueA (unique in this table never to repeat in another record in this table)

                               Table 2:

                                    record 1:

                                         asset_name: valueA (set by relationship where Table 2::asset_name = Table 1::asset_name)

                                         note 1: note text

                                    record 2:

                                         asset_name: valueA (set by relationship where Table 2::asset_name = Table 1::asset_name)

                                         note 2: note text

                Table 1:

                      record 2:

                          asset_name: valueB (unique in this table never to repeat in another recordin this table)

                              Table 2:

                                    record 1:

                                         asset_name: valueB (set by relationship where Table 2::asset_name = Table 1::asset_name)

                                         note 1: note text

                                    record 2:

                                         asset_name: valueB (set by relationship where Table 2::asset_name = Table 1::asset_name)

                                         note 2: note text

                 

                The question I have left is, how do I actually set up the relationship in the relationship tab of the database manager such that I can add the table using the portal tool to the layout for Table 1?

                 

                In other words, if I'm in the layout for Table 1 and I have used the Protal tool to add the records from Table 2 to the layout, how do I set up the relationship so that when I am looking at valueA I see all the notes from that record and none of the notes from valueB and vice-versa?

                 

                There are several different options in the Relationships tab and I'm not sure which to use.

                 

                Thanks!

                 

                -Dan

                • 5. Re: Import unknown number of notes and display with single record
                  keywords

                  A couple of things (well five as it turned out):

                   

                  1.     Ideally you wil NOT use the asset name as the unique ID because of the prospect of these not being unique (there may be two fire trucks, for example, and you will have to manually ensure that they are named differently to maintain uniqueness, a dodgy method).  Best practice is to have a separate AssetID field which atuomatically populated with, say, a serial number; use this field as the primary match field in your FM relationships.

                  2.     In your notes table you will also have an AssetID field, but in this table it will not be unique; it is a foreign key field whose purpose in this table is to match each note record to its related asset record.

                  3.     If each note relates to only one asset, then the relationship is a one to many type (one asset >> many notes), so you can set it up as AssetID_inAssetTable = AssetIDinNotesTable.

                  4.     On your chosen layout in your assets table, you simply create a portal based on this relationship, insert the required fields in the portal and all related notes will be displayed.

                  5.     If it is possible for a note to relate to more than one asset (a fire truck and a fire hydrant and a ladder, say) then it becomes a many to many relationship, and you will need to create a join table to manage that (or some other method, like multiple values in the foreign key field).

                   

                  Hope that's of some help.

                  • 6. Re: Import unknown number of notes and display with single record
                    strngr12

                    Got it and it works, thank you so much!