9 Replies Latest reply on Jan 17, 2015 7:24 AM by RB

    Help with displaying portal data



      Help with displaying portal data


      hello all, very new to FM and just purchased FM pro 13. i have a database that includes a list of medications that a patient is on. i used a portal in order to enter each medication (name) as well as the dose (oral) and frequency (daily) - which are all seperate fields. the user can enter a new row for each medication. i am having trouble with displaying this in a report for all the medications entered. i want the report to display each string of fields seperated by a comma for each new medication. Eg. "Aspirin 81mg oral daily, Tylenol 1g oral three time  day". Is this possible? i have been struggling with this for a few days now. i can get them displayed as a list (new lines) or just the first entry. any help is appreciated. 

        • 1. Re: Help with displaying portal data

          Substitute ( List ( PortalTable::Field ; ¶ ; ", " ) )

          • 2. Re: Help with displaying portal data

            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. 

            Any advice?

            • 3. Re: Help with displaying portal data

              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.

              • 4. Re: Help with displaying portal data

                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?

                • 5. Re: Help with displaying portal data

                  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.

                  • 6. Re: Help with displaying portal data

                    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.

                    - RG>

                    • 7. Re: Help with displaying portal data

                      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?

                      • 8. Re: Help with displaying portal data

                        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.

                        • 9. Re: Help with displaying portal data

                          Amazing! Thanks folks. Worked perfectly. Much appreciated.