1 2 Previous Next 15 Replies Latest reply on Jul 27, 2012 4:32 PM by philmodjunk

    Creating a relationship from a specific cell?

    GaryHobish

      Title

      Creating a relationship from a specific cell?

      Post

      Within a FM 10 database, I am trying to create a portal to a tab-delimited text file generated by another program. The formatting of the text file was not designed for this. I beleve to make it work, I need to establish a relationship between a field in the FM database and specific cell of the text file (in Excel parlance, cell B2). Only a certain number of records (rows) from the text file will appear in the portal (starting with record #9 through an indeterminate number depending on each file generated by the external program).

      In the attached example, only rows 10-16 should show up in the portal. Other .txt files might have any number of rows starting with CD Track ID#001 and anything from the row named "Region List" down should be ignored. I am relating the record in the FM database to to B2, in this case "Water254.txt."

      Is it possible to do this without pre-processing the text file? Am I making any sense?

       

      Thanks for the input.

      CDPQ_txt.png

        • 1. Re: Creating a relationship from a specific cell?
          philmodjunk

          Don't you want rows 9 - 15? In otherwords, the rows where the first column contains the values 001 through 007?

          I don't think you need a relationship, there's no real common value here that would allow you to only show those records in your portal or in a found set.

          You don't need to preprocess the file--though it would simplify things, but I would at least use a script to process this data after import to delete the unneeded rows. If any of the data in the other rows are needed, the script can move them into other fields/records/tables as needed.

          Note: If this data is from an excel spread sheet and you can define a range for these rows, you can import just the range starting with the row with the column labels and then FileMaker can use the column labels for field names.

          • 2. Re: Creating a relationship from a specific cell?
            GaryHobish

            Thanks for the response, Phil. It's definately possible I'm overthinking this, but let me play this out:

            Don't you want rows 9 - 15? In otherwords, the rows where the first column contains the values 001 through 007?

            Yes, that's correct. I miscounted the grid. However, while I may always start with row 9, the number of rows needed will vary with each record (literally, as I am an audio mastering engineer trying to use poorly formatted data from a disc mastering application).

            I don't think you need a relationship, there's no real common value here that would allow you to only show those records in your portal or in a found set.

            Yes, there is- the string in B2 (in this example "Water254.txt") would be entered into the appropriate field in the database in order to pull the data.

            You don't need to preprocess the file--though it would simplify things, but I would at least use a script to process this data after import to delete the unneeded rows. If any of the data in the other rows are needed, the script can move them into other fields/records/tables as needed.

            Again, as the number of rows needed will vary this could be tough to script- unless I have the script recognize somehow that the import should stop beginning with the 3rd row before the string "Region List," or possibly stopping with the first blank row after row 9. I've done some scripting in the past, but it's been a while and not sure how I would do that.

            Note: If this data is from an excel spread sheet and you can define a range for these rows, you can import just the range starting with the row with the column labels and then FileMaker can use the column labels for field names.

            That does make an import rather than a relationship look like the way to go. BTW, it's not strictly from an Excel spreadsheet but from a tab-delimited text file. I guess the method then would be to replicate the layout for a track ID as a series of repeating fields (up to the max number of expected rows), and then have the print engine in FM set up to ingnore the repeating fields that are blank.

            • 3. Re: Creating a relationship from a specific cell?
              philmodjunk

              Yes, there is- the string in B2 (in this example "Water254.txt")

              Not what I meant. That common value would have to be in all the rows where you want to see the data in the same column and not in that column for any of the rows where you don't want to see the data.

              A script can delete records 1-8 then loop through the remaining records until it reaches the first were columnn 1 is empty and then delete it through the end of the set of records.

              and then have the print engine in FM set up to ingnore the repeating fields that are blank.

              "print engine"? "repeating fields"?

              I don't know what printing has to do with this and I don't see any repeating fields here. Each row imports as a separate record into a table where the fields appear to be named: DSP_Quatro_Playlist, f2, f3, ...

              • 4. Re: Creating a relationship from a specific cell?
                Sorbsbuster

                By 'blank Repeating field' (a Filemaker term) do you mean 'the fields that are repeatedly blank'?

                • 5. Re: Creating a relationship from a specific cell?
                  GaryHobish

                  Yes, there is- the string in B2 (in this example "Water254.txt")

                  Not what I meant. That common value would have to be in all the rows where you want to see the data in the same column and not in that column for any of the rows where you don't want to see the data.

                  I see.

                  A script can delete records 1-8 then loop through the remaining records until it reaches the first were columnn 1 is empty and then delete it through the end of the set of records.

                  I can see that too, in an if...next loop.

                  and then have the print engine in FM set up to ingnore the repeating fields that are blank.

                  "print engine"? "repeating fields"?

                  I don't know what printing has to do with this and I don't see any repeating fields here. Each row imports as a separate record into a table where the fields appear to be named: DSP_Quatro_Playlist, f2, f3, ...

                  Actually, printing a formatted report is the entire purpose of this exercise. And if I'm importing, I don't want each row as a seperate record. A form of what appears above in rows 9-15 is the basis for each record. Example: row 9 columns 1-9 would appear as a row with 9 fields. Subsequent rows of data would be imported into a set of repeating fields, the number of fields equalling the number of rows for that document. I would apply "sliding up based on objects above" and "resize the enclosing part" on the layout, so the printed form does not include repetitions that are blank.

                  Every new document imported would be a new record, not the individual rows.

                   

                  I think I have the method down now, thanks for your insight.

                   

                  • 6. Re: Creating a relationship from a specific cell?
                    GaryHobish

                    to Sorbuster:

                    By 'blank Repeating field' (a Filemaker term) do you mean 'the fields that are repeatedly blank'?

                    No, I am actually referring to setting this up where a row and it's associated columns are defined as repeating field. In case it's unclear, each document imported could theoretically have anywhere from 1 to 99 rows of track IDs. I would set up my database at expect this, import the data from each text document, then have any repetitions left blank ignored when printing.

                    • 7. Re: Creating a relationship from a specific cell?
                      Sorbsbuster

                      It's not a use of repeated fields that would normally be recommended, and I would have thought you'd find it a bit of work to make imported text data populate field repetitions correctly.  But from your previous post you seem to have it sorted.

                      • 8. Re: Creating a relationship from a specific cell?
                        GaryHobish

                        You're right, I will have to see about populating the fields correctly but I think having a row of 9 fields with up to 50 repetitions might a better alternative (and easier to make a clean looking printed report) than say, having 50 rows of 9 fields (that's 450 unique fields), most of which will be empty most of the time (I expect anything over 14 rows will be rare).

                        That is what I thought I could conquer by treating this as a relational DB rather than importing. If there is a third alternative I haven't thoguht of I'd love to hear it!

                        • 9. Re: Creating a relationship from a specific cell?
                          philmodjunk

                          I strongly recommend against using repeating fields. Not only is this harder to set up for your import, it's unecessary. FileMaker report layouts can be structured to print multiple records on the same page.

                          • 10. Re: Creating a relationship from a specific cell?
                            GaryHobish

                            I appreciate your input and welcome your suggestions, but I don't think you're getting what I need to do. There will be only 1 record per page. That page must contain all of the data rows from 9 to x, where x Can vary depending on the number of tracks on the CD.  The record is the CD; the rows represented above (from 9 up) represent various data for each track on the CD. The tracks are not seperate records.

                            I realize the difficulty in importing to repeating fields, that is why I started to go down the relational route. I'm not interested in doing a layout with, say, 450 unique fields (possible 50 tracks x 9 columns), especially when a typical CD doesn't contain more than 13 or 14 tracks and usually less than that. If there's another solution I'd welcome the suggestion.

                            • 11. Re: Creating a relationship from a specific cell?
                              philmodjunk

                              I get exactly what you are describing and what I am recommending does exactly that and it is a routine function of nearly any relational database--and that includes FileMaker.

                              What you need are TWO tables. One is the Parent record, the one record per page that you are describing, the other is a related table that where you import this data with one row to each related table.

                              You then have several options for printing all this data on a single page. The simplest is to use a portal to the related tracks data.

                              • 12. Re: Creating a relationship from a specific cell?
                                Sorbsbuster

                                I think your maths is a bit suspect.  Using related tables with one relationship as per Phil's recommendation:

                                One CD =

                                - 1 Header Record ('The CD').  Maybe a coupla fields.

                                - 1 Track record with 9 fields

                                The idea of ever having to create 450 fields isn't real.

                                • 13. Re: Creating a relationship from a specific cell?
                                  GaryHobish

                                  PhilModJunk wrote:

                                  I get exactly what you are describing and what I am recommending does exactly that and it is a routine function of nearly any relational database--and that includes FileMaker.

                                  What you need are TWO tables. One is the Parent record, the one record per page that you are describing, the other is a related table that where you import this data with one row to each related table.

                                  You then have several options for printing all this data on a single page. The simplest is to use a portal to the related tracks data.

                                   

                                  OK, thanks for your patience. It's been a while since I worked with tables in Filemaker, I'm on the right road now.

                                  • 14. Re: Creating a relationship from a specific cell?
                                    GaryHobish

                                    I think your maths is a bit suspect.  Using related tables with one relationship as per Phil's recommendation:

                                    One CD =

                                    - 1 Header Record ('The CD').  Maybe a coupla fields.

                                    - 1 Track record with 9 fields

                                    The idea of ever having to create 450 fields isn't real.

                                     

                                    Sure isn't! Was never really a consideration.

                                    Got it working relationally now (no repeating fields), just need to get the scripting in order so it throws out unneeded rows and inserts the right unique ID in the match fields. Also need to figure out a way to do a calculation based on CD Frames, which is minutes:seconds:cd_frames. There are 75 CD frames per second. Don't think there's a built-in function for that.

                                     

                                    Thanks both of you for the help. Been a while for me!

                                    1 2 Previous Next