Formatting Numbers w/ Dashes?

[VA12]

In Excel, one can enter a custom format for a number, e.g.

 

0-00000-00000-0 or #-#####-#####-#

 

So that one sees 0-12345-67891-1 when one enters just the digits with no dashes.

 

Can this be done simply in a report layout in FPM? It's for use w/ UPC codes.

[AHunter3]

Not unless the places where you want the dashes are places where numbers would typically show commas.

 

1,000,000 could be rendered as 1-000-000 by making a dash your "thousands separator".

 

Other than that, you'd need a calc field of result type TEXT (not number) and then use middle and position to snip pieces out and intersperse dashes where you want them.

 

Let (num = GetAsText (NumberField);

Left (num, 1) & "-" & Middle (num, 2, 5) & "-" & Middle (num, 7, 5) & "-" & Right (num, 1)

)

[David Head]

While FileMaker Pro does not have input masks like Excel, it does provide a feature to format input. It is a field option - an auto-entered calculation that replaces the existing data.

 

There are a lot of ways to do this but here is a calculation that provides not only formatting but also some error-checking for input (too many or too few digits).

 

In this case, the field would be defined as a text field.

 

Let ([
digits = Filter (Self; "0123456789");
len = Length (digits);
red = RGB (160;0;0);
black = RGB (0;0;0);
mask = Left (digits; 1) & "-" & Middle (digits; 2; 5) & "-" & Middle (digits; 7; 5) & "-" & Middle (digits; 12; 1);
format = TextColor ( mask; black )
];

Case (
len = 12; format;
len < 12; digits & TextColor ( "*short" ; red ); 
len > 12; format & TextColor ( Right ( digits; len - 12 ) ; red )
)
)

[VA12]

Thank you, I'll give that a try.

 

Does it matter that in this case the field in question is the result of two fields - an 11 digit UPC field, and a calculated check digit field?

 

The field I'm trying to format in my report is called UPC12. It's a combination of fields UPC11 and UPCCheckDigit. UPC11 is imported or entered manually and required to contain 11 digits. It may, and often does, contain a leading zero. It matters not at all to me that the dashes for UPC12 be stored in the field, only that they show up on the report layout, though that doesn't sound like an option.

 

It's obvious that this is the meat of what I'm going for.

 

mask = Left (digits; 1) & "-" & Middle (digits; 2; 5) & "-" & Middle (digits; 7; 5) & "-" & Middle (digits; 12; 1);

 

I'm thinking I can just tack that onto my calculation for combining UPC11 and UPCCheckDigit?

[David Head]

Yes, since UPC12 is a calculation field, you could use this as your calculation:

 

Let ([
digits = Filter (UPC11 & UPCCheckDigit; "0123456789");
len = Length (digits);
red = RGB (160;0;0);
black = RGB (0;0;0);
mask = Left (digits; 1) & "-" & Middle (digits; 2; 5) & "-" & Middle (digits; 7; 5) & "-" & Middle (digits; 12; 1);
format = TextColor ( mask; black )
];

Case (
len = 12; format;
len < 12; digits & TextColor ( "*short" ; red ); 
len > 12; format & TextColor ( Right ( digits; len - 12 ) ; red )
)
)

 

Or if you are happy that there is no error checking needed:

 

Let ([
digits = Filter (UPC11 & UPCCheckDigit; "0123456789");
len = Length (digits);
mask = Left (digits; 1) & "-" & Middle (digits; 2; 5) & "-" & Middle (digits; 7; 5) & "-" & Middle (digits; 12; 1)
];
mask
)

[VA12]

Beautiful, thank you very much.