12 Replies Latest reply on Oct 27, 2015 2:06 AM by Mike_Mitchell

    portal info in merged field

    DenBenny

      Hi,

       

      Is there a way to put all portal info into a merged textfield?

       

      I want to input data into a portal and afterwards this data has to be in a printed report.

      I want to use a merged text field for the creation of this report because of auto spacing in the report (portal print is not a nice solution i think).

       

      thanks

        • 1. Re: portal info in merged field
          Mike_Mitchell

          DenBenny wrote:

           

          ...portal print is not a nice solution i think.

           

          thanks

           

          Well, you're right about that.  

           

          It depends on whether you want just the first record in the portal, or multiple records. If you want just the first record, you can just use <<relatedTable::relatedField>> on the layout. If you need multiple records, then you should base your report on the related table and use the fields from the parent table as related fields.

           

          HTH

           

          Mike

          • 2. Re: portal info in merged field
            DenBenny

            It is usually from multiple records and the number of records is always different.

            From the related table i want info out of (always the same) 3 fields into my report (that is in another table, the main table of the database). So i want to get a listing of a field (for al related records trough the portal) in my report.

             

            I use a portal in my main table to easely input some data.

            Because the number of data is always different, the use of a portal instead of fields seemed the best option to me, or is there a better solution?

            My first attempt was with fields but this ended very complicated so i start over with portals.

            • 3. Re: portal info in merged field
              Mike_Mitchell

              You would find (using a Find request) the records in the related table you want on the report. You can address the fields you need from the parent table using parentTable::parentField notation.

               

              It's entirely appropriate to use a portal for data entry. So you did fine there. Just use a different layout to print the report.

              • 4. Re: portal info in merged field
                DenBenny

                Ok, I understand that I must first search the related records of the portal table.

                But when i put <<portaltable::portalrecord1>> in the merged text field of my report (in the main table) i get only 1 field value and not 3 (when there are for example 3 records).

                • 5. Re: portal info in merged field
                  Mike_Mitchell

                  You need to use a list view on the related side to show multiple records.

                  • 6. Re: portal info in merged field
                    DenBenny

                    Sounds convincing, but it does not seem to be working here...

                     

                    what i did:

                    - i go from the main table to the portaltable

                    - i set the view to list view

                    - i did a search on the related field (relation with the main table)

                    - i get 3 results (in my test) and they are listed below each other.

                    - i go back to the main table (to the report layout (which is in form view)) and again i get only 1 result in the related field.

                    • 7. Re: portal info in merged field
                      siplus

                      You can get all the data you need in $$Variables with an eSQL in the Report script and put the $$Variables as merge variables on the layout.

                      • 8. Re: portal info in merged field
                        DenBenny

                        I'm not familiar with eSQL, can you give a example of your idea?

                        • 9. Re: portal info in merged field
                          Mike_Mitchell

                          DenBenny wrote:

                           

                          - i go back to the main table (to the report layout (which is in form view)) and again i get only 1 result in the related field.

                           

                          This is your error.

                           

                          You report layout should not be in Form view, nor should it be in the main table. It should be in List view, in the related table.

                           

                          Build a new layout based on the related table. Display it in List view.

                           

                          You might want to take a look in the starter solutions for examples of the proper technique. For example, the Invoices solution, the very last layout.

                          • 10. Re: portal info in merged field
                            siplus

                            = ExecuteSQL.

                             

                            Do a search on it, discover a new dimension.

                            • 11. Re: portal info in merged field
                              DenBenny

                              Mike_Mitchell wrote:

                               

                              This is your error.

                               

                              You report layout should not be in Form view, nor should it be in the main table. It should be in List view, in the related table.

                               

                              Build a new layout based on the related table. Display it in List view.

                               

                              You might want to take a look in the starter solutions for examples of the proper technique. For example, the Invoices solution, the very last layout.

                               

                              I can't do this because there are 2 portals in my main table.

                              Therefore i have to find a way to get the portal data to my main table.

                              • 12. Re: portal info in merged field
                                Mike_Mitchell

                                DenBenny wrote:

                                 

                                Mike_Mitchell wrote:

                                 

                                This is your error.

                                 

                                You report layout should not be in Form view, nor should it be in the main table. It should be in List view, in the related table.

                                 

                                Build a new layout based on the related table. Display it in List view.

                                 

                                You might want to take a look in the starter solutions for examples of the proper technique. For example, the Invoices solution, the very last layout.

                                 

                                I can't do this because there are 2 portals in my main table.

                                Therefore i have to find a way to get the portal data to my main table.

                                 

                                 

                                Well, mentioning that right away might have shortened the discussion ...  

                                 

                                I'm going to assume these two portals are from different related tables (otherwise, going from the single child table will still work). If that's the case, you can do a couple of things:

                                 

                                1) You can use siplus's technique and pull the data via ExecuteSQL and use the merge variables that way. Here's a good primer on using ExecuteSQL:

                                 

                                The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

                                 

                                2) You can use the Virtual List technique to gather the data into global variables and then display them in a calculated table. There are plenty of resources on the web about the Virtual List technique, but basically it looks something like this:

                                 

                                     GetValue ( $$var ; $row )

                                 

                                where the $$var is a return delimited list of the values and $row is the row number. This calculation is created in an unstored field in a separate table. The net result is that each record in the table represents a row in the variable. These links can get you started:

                                 

                                Virtual List in 3 Easy Steps - MightyData

                                FileMaker Virtual List Technique - SeedCode

                                Virtual List | FileMakerHacks

                                 

                                HTH

                                 

                                Mike

                                 

                                P. S. You don't necessarily have to put the data on a layout based on your main table. What you have to do is aggregate the data from multiple tables onto a single layout. That layout can be based on a child table, on the main table, or on a completely different, single-record table that has nothing but merge variables on it. It's important to break the paradigm you have there, because it's cramping your thinking.