8 Replies Latest reply on Dec 20, 2013 10:37 AM by RichardStrohman

    Repeating Field?

    RichardStrohman

      Title

      Repeating Field?

      Post

           Hello all,

           I am working with a database I made last year when I first started using Filemaker.  When I first started using Filemaker I did not know how to work with multiple tables and portals.  I have since learned, but now I need to change something on the old database.

           I was wondering if there was a way that I can show multiple fields of certain records.  What I have is a database that stores information of fiberglass tanks.  So when I have a order I enter in the PO number of the order along with some other information including the size of the tank.

           I would like to have a layout that shows the different tank sizes of a PO number in one field.  The different size tanks are each on their own record though.  (For example: I enter a PO Number for three tanks.  That is three different records.  When the first record is entered it is just duplicated and then the tank info is changed)

           Is there a way to do what I am wanting?

        • 1. Re: Repeating Field?
          philmodjunk

               What problem do you solve by putting it all in one field? (This can be done, but knowing the reason for doing it this way helps me specify the details.)

               List ( RelatedTable::Field )

               Can pull all the values of Field from the set of related records into one field. Substitute can be used with this function to replace the returns with other characters if necessary.

               And in FileMaker 12 or newer, ExecuteSQL can do the same but also offers additional options for manipulating what values appear in the field and how they are listed.

          • 2. Re: Repeating Field?
            RichardStrohman

                 When this order is entered in an email is sent to our drafting department that only states at the moment "A new order has been entered."  What I would like to do is show a list of all the tanks on the PO and send a PDF along with the email.

            • 3. Re: Repeating Field?
              philmodjunk

                   Then it would appear that the list function is all that you need for this unless each row in your list needs to combine data from multiple fields.

              • 4. Re: Repeating Field?
                RichardStrohman

                     Would I then, make a new field as a calculation with the above "List ( RelatedTable::Field)"? 

                • 5. Re: Repeating Field?
                  philmodjunk

                       You could, but you don't have to. In the Send Mail Dialog, you can specify a calculation for the body of the email. You can just use the List function in that calculation.

                  • 6. Re: Repeating Field?
                    RichardStrohman

                         You will have to forgive me.  I guess I am not sure on how to get this to work.  I have a field called TankSizeList that is a calculation field doing the "List (Table::Field)" and it is not doing anything.  I will try to explain again what it is I have and am trying to do, hopefully more clearly.

                         There is only one table for the whole database.  Like I said earlier when I create a new record, I enter the PO number and fill in the tank size.  If there are multiple tanks with the PO I just duplicate the record and then change the tank info.  So if there are three tanks then I have three records. I can find all three seating the PO and it pulls them up.

                         Now I have a new layout (same table as the PO and tank info) that I want to show a list of the different tank sizes for one PO.

                         Again you will have to forgive me.  I am still very new to this.

                    • 7. Re: Repeating Field?
                      philmodjunk
                           

                                There is only one table for the whole database.

                           That does not sound like a workable database design for what you are describing here. Multiple tables linked in relationship, among other things, will eliminate the need to duplicate as much data from record to record.

                           But working from what you describe, it's still possible to get this to work. But what I am describing is a bandaid on top of a system that could benefit from a significant redesign.

                           List won't work as I have described without a relationship. You need a relationship that matches to specific records--all the records for one PO and then List ( RelatedTable::Field ) will return the required list of values needed in your email body. But you do not have to add a new table to do this as you can link a table to itself in a relationship:

                           Open Manage | Database | Relationships

                           Click the box named TankSizeList (It's called a table occurrence) that you find here to select it.

                           Click the duplicate button to make a new copy of this table occurrence. You have not actually duplicated your table, you've just created a second table occurrence--adding a new way to refer to the records in that table. Now use your mouse to drag from the PO field in one table occurrence to the PO field in the other. This will then produce this relationship:

                           TankSizeList::PO = TankSizeList 2::PO

                           This then allows you to use:

                           List ( TankSizeList 2::TankSize )

                           to get a list of the values in TankSize for all records with the Same value in PO as that of the current record.

                           Note: in a DB with a better Data Model, you'd set it up this way:

                           Customers----<POs------<TankSizeList>-----Tanks   (If each Tank is a custom build, you won't need a table for Tanks)

                           Customers::__CustomerID = POs::_FkCustomerID
                           POs::__pkPO_ID = TanksSizeList::_fkPO_ID
                           TankSizeList::_fkTankID = Tanks::__pkTankID

                           My "bandaid" takes a second Tutorial: What are Table Occurrences? of TankSizeList and uses it in place of POs.

                           For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                      • 8. Re: Repeating Field?
                        RichardStrohman

                             That worked perfectly.  Thank you.

                             Since I made this last year I have made a new version that uses tables, relationships, etc...  Unfortunately the people using the old one didn't want to change so I am stuck with this.  Hopefully there won't be anymore changes.