Importing Currency Fields in Foxtrot

How to preserve dollar amounts for fields containing decimals

When importing currency amounts into Foxtrot from an Excel spreadsheet, Excel will automatically drop any trailing zeros after the decimal. If the cell shows 99.00 for example, the formula bar within Excel suppresses the zeros automatically. This will also happen after any decimal that is a multiple of 10, 458.10 will import as 458.1 for example. Changing the field type in Excel will not alter the value in Foxtrot’s Data Wizard.

If your target application requires the number to retain a typical currency format (99.00, 458.10, etc…), Foxtrot must be instructed to do so by using a Format Number Formula.

Resolution:

On Step 3 of the Data Wizard, verify that the Field’s Type is configured as Text. Clicking on the pencil icon will enable you to change the Name, Type, and Length.

Once imported, a Format Number Formula must be incorporated. Click on the Formula action under Data in Foxtrot’s action list.

Click on Math and then FormatNumber

Using the Expression Builder, select the desired field for the Number parameter, and set the DecimalPlaces parameter to ‘2’

Click the ellipses button to save the results. For convenience, save the result to the same field. This will not affect the original dataset.