10 Replies Latest reply on Jun 21, 2012 2:14 PM by philmodjunk

    Alphanumeric decimal in Portal not displaying properly

    GrimlockHale

      Title

      Alphanumeric decimal in Portal not displaying properly

      Post

      Hi,

      I built a database that has 3 different tables in it.

      Two of them have records of books and versions and the third is a customer table that takes its data from the other two.

      The reason there are 2 different ones to start with is because they are different types of books with different categories and sub categories. 

      Now, the way the third (customer) table works is, user picks a customer name (from a preexisting value list) then picks the book name from a popup menu. Then based on that selection, the user is given the available Languages for that particualr book including only related values. Then the user is given the available versions for that particular Book AND Language including only related values through an instance of the third (customer) table.

      All the data here is displayed properly. 

      The problem begins when I create a portal that shows which books each customer purchased. The book name displays correctly, language displays correctly but the version is jumbled.

      The version is an alphanumeric decimal (even though it usually doesn't have a letter, sometimes it does). In the tables it is displayed as 1.00, 1.10, 1.20, 5.33, 1.31a etc. but in the portal they will display (respectively) as: 1, 1.1, 1.2, 5.33, 1.311 etc.

      Any help would be greatly appreciated, I hope I've provided enough information.

      Thanks!

        • 1. Re: Alphanumeric decimal in Portal not displaying properly
          GrimlockHale

          I ended up solving the issue myself, I must have a poor undestanding of how Filemaker stores and reads data or how it treats data in and from popup menus.

          What I did was, I created a calculation field (text) in the customer table that equaled the problem field, this gave me the values as they were displayed in the portal.

          At this point I realized that the error was not originating at the portal but right here. So I came up with a couple of if statements that omitted and added characters where needed to display the versions correctly:

          "If ( Length ( GetAsText ( Book 1 Version ) )  = 5 ; Left ( GetAsText ( Book 1 Version ) ; 4 ) & "a" ; If ( Length ( GetAsText ( Book 1 Version ) )  = 3 ; GetAsText ( Book 1 Version ) & "0"; If ( Length ( GetAsText ( Book 1 Version ) )  = 1 ; GetAsText ( Book 1 Version ) & ".00" ; GetAsText ( Book 1 Version ))) )"

          And that was it, I just set the portal to read this field instead of the actualy popup field and problem solved.

          I hope this post helps anyone who somehow has a similar problem and if it looks like I went in a really roundabout way to solve a fairly simple problem, comments are welcome.

          • 2. Re: Alphanumeric decimal in Portal not displaying properly
            philmodjunk

            Is Book 1 Version a field of type text or number?

            I think it's either a field of type number or a calcualtion field set to return number.

            If it were text, 0 digits in values such as 12.0, 12.10 etc would remain visible in your field.

            • 3. Re: Alphanumeric decimal in Portal not displaying properly
              GrimlockHale

              Thanks for you reply,

              Book 1 Version is text

              • 4. Re: Alphanumeric decimal in Portal not displaying properly
                philmodjunk

                Then the zeroes should be visible if they were ever entered into that field in the first place.

                How does this field get a value? I would guess through a numeric calculation, which then will leave out the traling zeroes after the decimal.

                • 5. Re: Alphanumeric decimal in Portal not displaying properly
                  GrimlockHale

                  It gets the value from a popup menu, which gets its values from another table. In the "Use values from field:" -> "Specify field" I have both columns selected, the first field is a calculation field that returns a number and the second one is a number field (AHA!). I am including only related values starting from an instance of the current table (customer) and I'm showing values only from second field.

                  The reason I have it setup this way is that in the first table I have a script that finds out and alerts of the most current version. so in order to translate versions with "a" into a measurable number i had to translated them into .001 (1.31a would then be 1.311) and this way I could compare the highest version. I dont think I would be able to run the calculation if the field was set to text (or so I think, I setup this part of the database a couple of months ago).

                  Either way, I think we found the culprate which was the above number field.

                  • 6. Re: Alphanumeric decimal in Portal not displaying properly
                    philmodjunk

                    I suggest looking at how you create the value in the text field originally. I'd try to use a calculation that takes the number and appends any trailing zeroes there so that it is fixed for every case you need to display the value instead of adding a special calculation field to fix the formatting.

                    Here's a common calc for adding trailing zeroes after a decimal:

                    Let ( [ n = YourNumber ;
                              i  = Int ( n );
                              d = Filter ( Mod ( n ; 1 ) ; 9876543210 )
                            ];
                              i & "." & Left ( d &"00" ; 2 )
                          )

                    Set "text" as your return type. It appends any decimal and trailing zeroes needed to produce 2 digits after the decimal. The Left function's parameters can be modified for different numbers of places after the decimal.

                    • 7. Re: Alphanumeric decimal in Portal not displaying properly
                      GrimlockHale

                      Thank you for the message.

                      I tried adding the calculation field above to the original table (returning text) and the results (in the field) are fine (minus the versions that have a "a").

                      I setup the third table to draw the version from this calculation field and they appear correctly.

                      But the portal still displays incorrectly.

                      • 8. Re: Alphanumeric decimal in Portal not displaying properly
                        philmodjunk

                        and the results (in the field) are fine

                        and which field do you have in the portal? If it's the same field, then you should see the trailing zeroes. If it's a different field, then you need to look at how it gets data from the original field.

                        • 9. Re: Alphanumeric decimal in Portal not displaying properly
                          GrimlockHale

                          the same field via the customer table.

                          • 10. Re: Alphanumeric decimal in Portal not displaying properly
                            philmodjunk

                            I don't see how it's possible for "results are fine" on one layout and not the other if it's the same exact field. The value won't change just because you display it on a different layout.