Substitute ( List ( PortalTable::Field ; ¶ ; ", " ) )
Thanks for the reply. But still not working. The return value is blank. I have three list fields: med_name, med_dose, med_freq and the the user adds new medication and details. I still for some reason cannot get a formula to work to return the list in a sentence name dose freq for each drug.
You'll need to describe what you have and have attempted in more detail. This is a very commonly used method to turn a return separated list, such as you get from List ( relatedTable::Field ) into a comma delimited list.
What I described would be defined in a field of type calculation defined in the layout's table but referring to the portal's table in order to list all related records in this format.
Thanks for the reply. Hope this helps... I have a portal in a form that the user enters all medications in (see attached pic). In a report, I want to insert a merge field that lists all the medications entered. If I just use the variable "med name" in the merge field, only the first entry is there. I tried to use a summary field, and all the meds are then listed, but one per line. If I try the list function (list (med name)) only the first one appears.
My goal is that the in the report, the meds will be listed at Ramipril 2.5mg PO OD, ASA 81mg PO OD, etc.
I am hoping this is possible?
This is exactly what I expected and the method that I describe should be able to produce a list with the desired format.
A portal implies that you have two table occurrences, probably two tables, linked in a relationship.
You would define a field of type calculation in the Layout's table set to evaluate from the context of the layout's table occurrence using this calculation:
Substitute ( List ( Medications::Name ) ; ¶ ; ", " ) )
Make sure to select a text result type.
If you then place this calculation field on your layout, you'll see a horizontal list of medication names. If you want to combine several fields from each portal record, define a field in the portal table that combines them and then use the above function on that added calculation field.(Or you can use ExecuteSQL() )
This approach assumes that you are NOT using a filtered portal.
That's what I'd do, too. In the same table that stores the medication data, I'd define a field (that doesn't appear on any layouts) which is a calculation field (call it MedReport for example) and the calculation for that field is:
Medication & " " & Dose & " " & Route & " " & Frequency
This would generate the individual entries that you want separated by commas, then you just need to combine them by referencing the MedReport field in the Substitute or List function instead of the med name field.
Thanks very much!
My trouble (which I think is the rate limiting step) is that when I use the List function alone (List (med_name)) only the first medication appears and not the list. If I use a summary field, all the meds are there.. so I know the data is somehow associated with the variable.
Am I doing something wrong with the association between the tables?
The list function needs to be defined in a calculation field in the layout's table and refer to the portal's table. If you define it in the portal's table, you'll get just a single item in your list.
Amazing! Thanks folks. Worked perfectly. Much appreciated.