5 Replies Latest reply on Aug 5, 2012 2:53 AM by Sorbsbuster

    Trouble with List Function using numbers



      Trouble with List Function using numbers


      Okay, after 10 hours of searching and trying, I need help.

      I want a list to appear on a layout form (in browse mode).  This list would include fields (text) and fields of corresponding amount totals (number).


      Field “Drawing Fee” (text) & Field Drawing Fee Total Amount (number).

      Field “Project Duration” (text) & Field Project Duration Total Amount (number)

      Field “Mileage Total” (text) & Field Mileage Total Amount (number)

      And so forth.

      If Drawing Fee is $0.00, then I want it not to show on the list, and Project Duration (proving the amount is greater than $0.00) would appear…

      I was able to create this using the “List” function, however, the numbers are text and do not show the cents (.00).  Is it possible to use the List function with numbers?  See attachment.

        • 1. Re: Trouble with List Function using numbers

          Sorry.  See attachment.

          • 2. Re: Trouble with List Function using numbers
            Hi Brent, Instead of multiple 'like' records (they all describe a Thing and an amount) they should be RECORDS with description (text) and Amount (number). If structured in true normalized fashion, all reporting and summarizing is easy.  In your example, you would find the type of records you wish (you can add a Type field) and specify amount <> o.  :-)
            • 3. Re: Trouble with List Function using numbers

              I do not understand your reply.  Please explain.

              • 4. Re: Trouble with List Function using numbers

                In your example, you have hard-coded the text into the field name.  And so you must create a second field for that field's amount.  And if you want to summarize Drawing Fees you will have to create a summary field specifically to summarize your Drawing Fee Amount and on it goes - two and even three (or more) fields for every VALUE (item) that you have.

                But you only need three fields to handle it all:

                ItemID (auto-enter serial number) - Item (text) - Amount (number) ... as fields (the bold is data in each record ...

                record 1 - ItemID 1 - Item: Drawing Fee - Amount: 1775

                record 2 - ItemID 2 - Item: Project Duration - Amount: 150

                record 3 - ItemID 3 - Item: Mileage Total - Amount: 1625

                You can them group, summarize, count and display each item however needed.  As you have it, you will be struggling to group your data and get reports as you currently are struggling.  Whenever you find yourself creating duplicate 'like' fields, it is usually an indicator that those fields should be related records instead. If records instead of fields, your list will be a simple ... list.  Perform a find to restrict to records with an amount, format the number (and the summary) as currently or decimal and display any report as you wish.   :^)

                • 5. Re: Trouble with List Function using numbers

                  ...and you display and filter them easily in portals.