4 Replies Latest reply on Jan 15, 2016 3:23 AM by IanJempson

    Format issue with PSoS

    Datastudio

      Hi,

       

      we have following problem.

       

      I’ve put together a sample file to test functionality. Scripts are setup for local and perform script on server (PSoS).

      We need to import data from one table to another but since PSoS does not allow direct imports, we first export data to .csv files and the import them back. Only difference is that local script exports file in ~/Documents directory, server script exports to /Library/FileMaker Server/Data/Documents directory.

      Local scripts are ok, but with PSoS there are problems with date and number formats.

      Data in .csv files after export is ok, decimal point is comma “,” so the number looks like “123,45”. But after import with PSoS, data in the field is “123.45” so FileMaker takes it as whole number “12345” in calculations.

      If I check apply layout data format in ExportRecords then the number in .csv is “123.45” and then when it is imported back it changes to “123,45”.

      This is not happening on all the systems, we have three servers with same language/format settings but on some works on others doesn’t.

      It looks like that fms keeps different formats than system, is this possible?

       

      Regards.

        • 1. Re: Format issue with PSoS
          CamelCase_data

          Have you considered using File > File Options... > Text > Always use file's saved settings?

          • 2. Re: Format issue with PSoS
            Datastudio

            We have tried but there is no difference.

            • 3. Re: Format issue with PSoS
              RubenVanDenBoogaard

              We have seen similar issues with formats of dates and numbers when I use a file created on a Dutch system (where we use , as a decimal separator) on a US system even if they use local settings.

               

              For us the solution was to create a clone of the database and open it on the US machine so it stored the US settings, so that might be the solution for you.

               

              You can also try to export and import the file to a FM file instead of csv, then FM server does not have to read from a textfile and does not have to translate the text into numbers and dates

               

              Hope that helps

               

              Ruben

              • 4. Re: Format issue with PSoS
                IanJempson

                yes. FileMaker Server runs everything with US formats. Major pain, but it's been that way for years.

                 

                Re. workarounds, you basically need to know if the script is running on the server or in a client and deal with the imports/exports accordingly. You can do your export from a layout that's US formatted and export applying the layout styles on export. Then when you import it will come in correctly. You do end up with two branches of code doing exactly the same thing with one minor variation which is not ideal as it increases the risk of bugs.

                 

                best thing to do is report it as a bug and submit a feature request to change the behaviour. Having said that - i've not tested to see if 14 has fixed this.