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.
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.
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.
Would I then, make a new field as a calculation with the above "List ( RelatedTable::Field)"?
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.
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.
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
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.