7 Replies Latest reply on Oct 11, 2011 9:58 AM by MarkHemans

    Repeating Field's question



      Repeating Field's question


      I am trying to take data in a field (that has up to five strings of digits separated by a space) and move it to a repeating field. I would like to do this with a calculation. The main reason I am doing this is because I am trying to build a relationship between data in this database and a new one. Right now the relationship only allows it to match up to the first string of text in that field, but I might want one that is the third one in.


      Any help would be appreciated. Maybe there is an approach that I haven't thought of yet.

        • 1. Re: Repeating Field's question

          This doesn't sound like a good use for a repeating field. A table of records, one record for each repetition in your repeating field would seem a much better approach for this. Repeating fields these days have only certain very limited uses as a related table of records is a much better option in nearly every case.

          • 2. Re: Repeating Field's question

            I understand what you are saying, but doesn't that add more complexity to a simple database?

            • 3. Re: Repeating Field's question

              Actually, from the database point of view it makes things more simple.  Having the separate records makes the data manipulation and presentation much easier and well worth the trade off of having more records.

              • 4. Re: Repeating Field's question

                I would agree with that, but either way I have to split up the current data without too much of a hassle. So, how do I go about splitting up the one field and make it multiple records?

                • 5. Re: Repeating Field's question

                  When you import records to move them from the current table into the new table, there's an option you can select in the very last dialog to pop up just before you kick off the import that will tell Filemaker to split the repetitions into separate records. Thus, you can do the following with a copy of your database file:

                  Define an auto-entered serial number field to unqiuely identify each record in your current table if you do not already have one. Place this new field on a layout where you can put the cursor into this field, then Show All Records and use Replace Field Contents and it's serial number option to load all existing records with a unique serial number and to also update "entry options" at the same time so any new records in this table will get the next number in the series.

                  Now use Import Records with the New Table options selected in the Target Table drop down. Specify only the new Serial Number field and the repeating field as the fields to be imported into the new table. Import the records and use the option I mentioned to split the data into separate records.

                  Go to Manage | Database | Relationships and link the new table to the original by the Serial Id fields. You may find it useful to enable "allow creation of records via this relationship" for the new table as this makes it easy to add new records in a portal to the new field just by entering data in the portal fields.

                  On each of your existing layouts where you have a the original repeating fields, replace the repeating field with a portal (if you want to arrange them vertically) or with a series of side by side one row portals ( if you want a horizontal row of fields ). Once you have updated all your layouts and can see the original data is now appearing in portal records, you can delete the original repeating field.

                  Now the original relationship you wanted to set up can be done by defining a link to the appropriate field in the new table instead of your original. (Note: the only alternative I know of would have been to define a set of individual non repeating fields in your original table, each with their own relationship to occurrences of the same related table--which is much MORE complex than setting up this related table.)

                  • 6. Re: Repeating Field's question

                    If your original data is sets of strings in a single field, you should be able to use text calculations to pull each those strings out.  It would depend on how those strings are formatted and how consistent they are. i.e. is each string exactly the same number of characters?

                    • 7. Re: Repeating Field's question

                      They are the same number of characters with a space separating them