6 Replies Latest reply: May 15, 2014 10:13 AM by BShipman _ RSS

    Setting Monarch V11 to always have 2 decimal places when importing a database

    BShipman _

      Hello,

       

      I am curious if there is an option within Monarch to set one's preferences so that every time one imports a database that numeric figures will automatically show 2 decimal places.  I open a lot of databases with many numeric fields and it can be annoying to have to go in and set the decimal places each time.  Thanks.

       

      Brad

        • Setting Monarch V11 to always have 2 decimal places when importing a database
          OddJob _

          Hi

           

          I don't think there's an option to set the number of decimal places to a default.

          Obviously, if you're performing the same task regularly, on the same data; creating a model allows you to set field properties for individual fields. These settings are then used whenever you open the data file and the associated model file.

           

          If you're performing ad-hoc tasks and are opening different data files each time, I believe Monarch inspects the first 250 records in the file and tries to determine the nature of each field i.e. is it characheter, numeric etc.

          If the first 250 records contain numeric fields with two decimal digits, Monarch should automatically set the field properties to numeric with two decimal places.

           

          Hope this helps

            • Setting Monarch V11 to always have 2 decimal places when importing a database
              BShipman _

              Hi

               

              I don't think there's an option to set the number of decimal places to a default.

              Obviously, if you're performing the same task regularly, on the same data; creating a model allows you to set field properties for individual fields. These settings are then used whenever you open the data file and the associated model file.

               

              If you're performing ad-hoc tasks and are opening different data files each time, I believe Monarch inspects the first 250 records in the file and tries to determine the nature of each field i.e. is it characheter, numeric etc.

              If the first 250 records contain numeric fields with two decimal digits, Monarch should automatically set the field properties to numeric with two decimal places.

               

              Hope this helps[/QUOTE]

               

              Thanks.

               

              I certainly set the decimals to 2 places for models, but also perform many ad hoc tasks.

               

              I think that you are right about reading the first 250 (or so) records, however, I've found that since switching to V11, it does not seem to do this anymore.  Maybe it has a different way of reading the column now?

                • Setting Monarch V11 to always have 2 decimal places when importing a database
                  OddJob _

                  Hi Brad

                   

                  I just did a quick test on this. If I'm opening csv or excel files, Monarch generally identifies the numeric field properties correctly - 2 decimal places (where appropriate).

                  When I try the same test with a Database (in my case, a table in Access), Monarch shows numeric fields with zero decimal places.

                  I've tried this in V11.1 and V9.01 pro. Both give the same results.

                   

                  I'm afraid this doesn't help you but, maybe it's always worked this way and I just hadn't noticed.

                    • Setting Monarch V11 to always have 2 decimal places when importing a database
                      RalphB _

                      From my experience working with Monarch and Access, Monarch reads whatever format your table has.  If the field has decimals, Monarch will display the field with decimals.  If it does not have decimals, you will have to use a calculated field to insert them.

                        • Setting Monarch V11 to always have 2 decimal places when importing a database
                          OddJob _

                          From my experience working with Monarch and Access, Monarch reads whatever format your table has.  If the field has decimals, Monarch will display the field with decimals.  If it does not have decimals, you will have to use a calculated field to insert them.[/QUOTE]

                           

                          After reading this reply, I went back to the table that I was importing from Access and took another look at it.

                          Each record has a field named Document Total. I inspected the field properties and found that the Decimal places attribute was set to Auto. I changed this to 2 to see if it made a difference; unfortunately it didn't.

                           

                          The first record has a value of -4.95 in the Document Total field. This displays correctly in Access.

                           

                          In Monarch V9.01 the import screen shows this as -4.950000 whereas in V11.1 the import screen shows it as -5

                           

                          In both versions of Monarch, when the data is imported, the Data View / Table View shows this as -5 with the field properties showing as Numeric with zero Decimal places.

                          If I change the Decimal places to 2, the value shows correctly as -4.95 in both V9.01 and V11.1

                           

                          So, I see this behaviour in both V9 and V11 , Brad has the problem in V11 but doesn't remember having it in previous versions and RalphB doesn't seem to encounter this problem at all.

                           

                          Can anyone shed any further light on whether the field properties should be correctly identified by Monarch when importing from Access or other Databases ?

                            • Setting Monarch V11 to always have 2 decimal places when importing a database
                              BShipman _

                              After reading this reply, I went back to the table that I was importing from Access and took another look at it.

                              Each record has a field named Document Total. I inspected the field properties and found that the Decimal places attribute was set to Auto. I changed this to 2 to see if it made a difference; unfortunately it didn't.

                               

                              The first record has a value of -4.95 in the Document Total field. This displays correctly in Access.

                               

                              In Monarch V9.01 the import screen shows this as -4.950000 whereas in V11.1 the import screen shows it as -5

                               

                              In both versions of Monarch, when the data is imported, the Data View / Table View shows this as -5 with the field properties showing as Numeric with zero Decimal places.

                              If I change the Decimal places to 2, the value shows correctly as -4.95 in both V9.01 and V11.1

                               

                              So, I see this behaviour in both V9 and V11 , Brad has the problem in V11 but doesn't remember having it in previous versions and RalphB doesn't seem to encounter this problem at all.

                               

                              Can anyone shed any further light on whether the field properties should be correctly identified by Monarch when importing from Access or other Databases ?[/QUOTE]

                               

                              Thank you for taking a look at this.  I am glad to know that I am not the only one seeing this!  I had tried to set the numeric properties in Access, to no avail.

                               

                              I will have to go back and compare the exact same file when opening in v9 as opposed to v11, but we have been opening databases, spreadsheets and comma delimited files for years, so I would be absolutely floored to find the issue when looking at v9 again.

                               

                              Hopefully someone has some ideas.