asked on

Formatting Numbers (Conditional or Other)

I have a table that I am trying to change to a vertical format. The original has a dozen or so line items. My problem arises in that the different line items can have different formats for their amounts (Percent, Currency, etc.)

What is the best way to format the Amount field appropriately? I have found a custom function on the "Filemaker Custom Functions" site, but that currently has an issue with the large numbers that I need to use (9+ digits). My other thought was Conditional Formatting, but that seems to only affect things like fonts, colors, underlining, etc.

Is there a way to format (Data Formatting section) a number conditionally? (Number, percent, numbers of decimals, etc)?

Thanks!

Will Loving

Number formatting is applied on a per field basis, so you should be able to select each field and set the formatting for the desired appearance. If the field is a calculation and needs to be conditional based on where the data originated or how it was calculated, then you'll probably need to do one of two things:

Revise the calculation for the field to use the various number formatting custom functions to set the correct format. My regular go to CFs for this are FormatNumbers, FormatPercent, FormatAsDate (or date.format), FormatTime, etc. The calculation result MUST be Text.
Use the Virtual List technique to set the value of each field - which must be text fields - with the number formatted how you want it to look.

If, that doesn't address it, respond with more detail and examples, especially about the source of the data and whether we're talking about number or calculation fields.

Blair Phifer

ASKER

Thanks for responding so quickly!

Right now, I am in the beginning stages of building an application, so everything is bare-bones.

I have tried several different custom functions, and all have given me the same results.

I have attached a copy of a part of the db where I am having the problem (Formatting Issue.fmp12). If you open up the "Formatting Numbers Issue" layout, this is the second record. The first record is a percentage, the "FormatNumber" function handles that properly. I haven't taken the time to clean up the other functions to deal with percentages, yet.

The second record has the problem. Here is the record without touching anything.

When you click on one of the bottom three fields, this shows up.

The same problem happens with all three of the functions shown at the bottom.

Here is the same form with a value with less than 9 digits.

The result is the same, and correct. It's odd that the 9th digit throws every function off. This is odd since the "FormatNumberAsText" function has this sample (More than capable of handling nine digits!)

Thanks!

ASKER CERTIFIED SOLUTION

Will Loving

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Blair Phifer

ASKER

I guess I need to look further at the "Data Normalization." I would prefer to have the line items be their own record, rather than having one record with multiple line items. I may have to change that one line item to a dollar amount from a calculation rather than a percentage, storing the percentage elsewhere. If I do that, then it solves my problem with formatting, since all will be Currencies.

Thanks for all your help!!!

Will Loving

There are folks with PhDs in relational database design who could talk to you for days about Data Normalization, for which there are clearly delineated rules and "forms" ( see Data Normalization - Wikipedia ) and the language can be very formal. However, the basic concept in relatively plain English is "a place for everything and everything in it's place", or....that 1) similar data should be grouped together in it's own fields and table (no apples and oranges, like dollars and percents in the same field), and that 2) data should not be replicated in multiple locations but rather stored in one place and referenced by unique keys that relate records to one another. The is much more but that's a good place to start.

For #1, that means that a Customers contact info exists in one place, the contact record. If you want to display it at the top of a letter or invoice, you use the unique Customer ID at the relational key to pull that address info from the Contact record to the invoice. (but of course they are always exceptions such as if you want to have the invoice show the customers address at the time the invoice was created, then you COPY the address to a field in the invoice table which then preserves it, but the primary address still only exists in the Contact record. The Invoice address is only reference for historical purposes to show where something was billed to in the past, even if the current address is different)

Normalization also means that you may have many related tables:

Customers > Invoices (one-to-many, using CustomerID)
Invoices > LineItems (one-to-many, using InvoiceID)
Invoices > Payments (one-to-many, using InvoiceID)
LineItems > Products (one-to-many, using ProductID)

If you want to see all the payment for one customer, you can "pull" that data "through" the Invoices, because Customers are related to Invoices and Invoices related to payments. However, it's sometimes convenient to create a direct relationship from Customer > Payments, which works as long as Payments records include the CustomerID which you can Auto-Enter upon creation from the Invoice. That breaks or bends a normalization rule and that is where ideal theory and the realities of working in a particular development environment like FileMaker meet. However, always strive for normalized structure and bend the rules consciously and judiciously.