8 Replies Latest reply on Feb 5, 2015 11:04 AM by planteg

    Report based on a virtual list




      I ma trying to create a report based on a virtual list as explained in FM 13 Advanced training course, paragraph Virtual Lists for Crosstab Reporting. I am having issues, and this drives me nuts .


      I have the right values in my virtual table, but they don't show up properly on the report.


      This is the content of the virtual table

      VTable 1.jpg

      Everything is as expected. But in the report layout, it's wrong.


      Issue 1, I get only one Projet (project) instead of 3.

      VTable 2.jpg

      Issue no 2, some numbers are either multiplied by 100 (3 125.00) or by 10 (395.00). See what happens if I click into one of those fields

      VTable 3.jpg

      Quite puzzling.


      Issue no 3: see what I get when I click preview

      VTable 4.jpg

      This time I get all of the Projets, but the Client name is repeated.


      I seriously think about quitting drinking water .


      But wait, there is more. I move my mouse over the only displayed Projet and roll the mouse wheel, I can get to other Projet. Since some may ask about the script, here it is

      VTable 5.jpg

      Thanks in advance to all of you who can throw some light in my nightmare.


      Gilles Plante

        • 1. Re: Report based on a virtual list

          Gilles -


          I can't say for certain, since I can't see the sort order, but I believe your issues are caused by two things:


          1) You have two sub summary parts on your layout instead of a body part. This is why you only see one item.

          2) Your number format is possibly set in a custom format as a percent, but without the "%" symbol?

          3) Your sort order is incorrect. If you sort first by Client name, then by Project, it should resolve correctly.


          Try that and see if it helps.



          • 2. Re: Report based on a virtual list

            Hi Mike,


            I recreated the Layout, this time with a sub-summary part on Client and body on Projet... same issue. Instead of sorting by Client and the by Projet, I sorted only by Client and got the Client displayed only one time. Strange but it works.


            Now for the numbers, I know what's wrong, but don't know yet how to fix it. I have an issue only when a number has a '.' in it. For example 30.75 is considered as 3075 for a number. Calculation for Sem_1 is

            VTable 6.jpg

            GetValue () returs a Text, but Calculation result is set to Number, so I thought I would get a Number. If I changed the calculation to GetAsNumber ( GetValue ( rowContents; 4) )... the it stores 3075 in the field. So I have a issue with numbers. This is something one can get with any tool, not only FileMaker.


            I run 13.0v5, I testes in 120.v4, same result. All the data is inside $$TotParProjPer, and it's set by ExecuteSQL(), Even though I would convert to number from within the ExecuteSQL query, I will get the output as a text. FileOptions, under Data  Entry is set to Always use current system settings. If I change it to use file's saved settings, same issue.


            To sum up, I can't get numbers in my five Sem_x fields. How can I achieve that ?




            Gilles Plante

            • 3. Re: Report based on a virtual list

              Gilles -


              ExecuteSQL always returns a text result. So that's a given.


              I'm not seeing the same result you are on just a quick test:




              Are you certain the row contains nothing but the numerals and decimal point?



              • 4. Re: Report based on a virtual list

                Hi Gilles,


                can you move this database to a Mac and see if you have the same number issues ?

                • 5. Re: Report based on a virtual list

                  Here are my findings.


                  I work on a copy of a database hosted on FMS on a Mac. The database was created on a Mac, with "," as the decimal separator. I work on a computer running Windows 7 where the decimal separator is set to ".".


                  To do a test, in the string returned by ExecuteSQL(), I substituted "." by ".". And it works.


                  Thing is I don't get is why I need to do that.It needs to be done whether in File > Options I choose to use System Settings or File settings.

                  Can someone explain ? I would like to create databases that work in all locales or situations.


                  Gilles Plante

                  • 6. Re: Report based on a virtual list

                    I'm assuming you substituted a "," (comma) with a "." (decimal point or period). If that's the case, then I can understand what FileMaker is doing.


                    If the decimal separator is set to the "." (period), then it will filter out the "," (comma) when it parses the string. It assumes that "1,23" is the same as "123" because the comma is irrelevant for purposes of determining the number.


                    If you like, you can choose "Use file's saved settings" under File > File Options > Text. That may force FileMaker to use the comma as the decimal separator. (I am NOT certain of that; experiment to be sure!)




                    • 7. Re: Report based on a virtual list



                      in the result I got from ExecuteSQL(), I changed "." into "," to get it to work. Now it's fine. My issue where I don't get all of the Projet is back . But Preview and Printing is Ok. I will find the fix for this one of these days .

                      • 8. Re: Report based on a virtual list

                        Just found why I couldn't see all records for my report: it was displayed in Form View instead of List View .


                        Gilles Plante