Sorting numbers into currencies in FileMaker is relatively straightforward when dealing with custom fields. However, complications arise when working with merge fields that require different formats in the same text block. For example, you may need to display one part of a number as input and another as currency. This article explores ways to apply correct formatting to multiple number fields in a single block of text.
The Challenge is
In FileMaker 2024, you are working with documents that combine static and variable data. Static data remains the same in all documents, while variable data is a FileMaker integration field. Code forms and other complex texts often require number systems such as currency, decimal, and “as entered” systems. The challenge is that a text block can only use one format for all of its number fields—whether currency, decimal, or “by default.”
The simplest solution: multiple blocks
The simplest solution is to use multiple fonts, each using a different format. However, this approach can be problematic for complex systems where the array of numbers cannot be separated by parentheses. Maintaining consistent slippage and spacing can be challenging, especially with frequent adjustments.
Mathematical Outlines
Custom functions are a useful way to control currency formatting within a single text. Here is an updated formula for a currency system that works perfectly in FileMaker 2024:
Let(
Number = GetAsNumber(number);
Precision = GetAsNumber(Precision);
Decimal = Decimal;
Separator = Separator;
Currency = Currency;
FormatNumber = Number;
Decimals = Case(Precision > 0; Decimal & Right(10^Precision & Text(FormatNumber * 10^Precision; “0000000000”); Precision); “”);
Thousands = Substitute(Text(FormatNumber; “##,###,###,###”); “,”; Separator);
Sign = Case(Number < 0; “-“; “”)
];
Sign & Currency & Thousands & Decimals
)
his formula provides flexibility with currency formatting and handles various number lengths and formats efficiently.
Reporting activities
Define custom functions in FileMaker 2024 for efficient currency processing These functions can be defined throughout your solutions, focusing on understanding structure. Here is an example of a custom function called NumberFormat:NumberFormat(number; “$”; “,”; “.”; 2).
This function can be performed for different formatting requirements. If your requirements are basic (e.g. just adding hearts and dollar signs), a simpler approach might suffice:
“$” & text(GetAsNumber(number); “##,###,###,###”);
Bonus: When the Loop Version
For those interested in more complex methods, here is a While loop version for sorting numbers.
While(
[
Number = GetAsNumber(number);
IntegerPart = Int(Number);
Remainder = Case(Mod(Number; 1) = 0; “.00”; Text(Mod(Number; 1); “0.00”));
Result = “”;
IntegerPart > 0;
[
Result = Right(IntegerPart; 3) & Case(not IsEmpty(Result); “,”) & Result;
IntegerPart = Left(IntegerPart; Length(IntegerPart) – 3)
]
];
“$” & Result & Remainder
)
This approach emphasizes the importance of simplicity in solutions. FileMaker 2024 offers a robust set of tools, and choosing the simplest option often results in an efficient and maintainable solution.
Conclusion
This guide addresses the complexities of using multiformatted number merge fields in FileMaker 2024. If you have other techniques or other insights on currency structure, please share in the comments
