In this example, I had to create a positive pay file for Fifth Third Bank. They require a CSV file and six pieces of data on the file. Each record should be on its own row on the file.
1) ChequeStatus indicator - this will be an "I" if the chequeStatus type is "Payment", it will be a "V" on the file if the type is "Void" or "Rejected" or "Cancelled" and it will be empty if there is any other type on the record in the file.
2) AccountNum
3) ChequeNum
4) TransDate - format should be MM/dd/yyy
5) BankNegInstRecipientName - need to strip out commas since it's a csv file and commas will add columns
6) AmountCur - this should not have any decimal or comma, but should always show the dollars and cents. So 1200.43 should be 120043. 2243.00 should show 224300. Etc.
You must create a new configuration format based on the data model Positive pay model. I am not going to explain how to do that here. There is plenty of Microsoft documentation that explains how to do that part.
Once you get to the configurations page, you should click "Designer" to build your file.
You have to build a hierarchy where you have a Type "File" at the top level. Then you add a Sequence and then you add a second Sequence. Like this:
File
RowSequence
FieldSequence
File should have encoding UTF-8. Under FILE EXTENSION SETTINGS you should select a Custom (user defined) way of definition and put "CSV" in the Custom extension box. This will generate a CSV file extension for you. If you wanted a .TXT file, you could put that here as well.
Under Formatting Preferences, I added the MM/DD/YYYY for my date format, although I don't know if this global format works. I ended up adding formatting to my date and didn't try it without the formatting.
Under the format for your RowSequence node, you should have special characters set to "New line - Windows (CR LF)". This will create a new line after each row on your file. This node is at the row level.
Under the format for your FieldSequence node, you should have Custom delimiter set to a comma (just put , in the box). This node is at the field level and putting this custom delimiter here will add a comma after every field for you. If you need a different type of file like pipe delimited, you could put your pipe, |, here.
Now you can start adding your fields to FieldSequence. Make sure you click on FieldSequence and click Add > String. Name it CHEQUESTATUS. No special format properties are required. Click on Mapping and then click on Edit formula. This is where you can write a formula for your field. Because ChequeStatus is an enum, this can be tricky. What I did was, first, create a formula for the field that shows: Text(@.ChequeStatus). Then you can run your file and see what shows in the CHEQUESTATUS field on the file. Once you know the text in the field, you can write an IF statement that does what you need:
The next two are easy because they are direct mappings to the fields in the model and no formula or mapping are required.
Make sure you saved your CHEQUESTATUS formula before moving on. Go to FieldSequence and add another string called ACCOUNTNUM. Click on Mapping, choose your field from the Model > Positive pay transactions (field is AccountNum) and then click "Bind". That's it for AccountNum.
Go back up to FieldSequence and add another string called CHEQUENUM. Click on Mapping, choose your field from the Model > Positive pay transactions (field is ChequeNum) and click "Bind". That's it for ChequeNum.
The next field we want is TransDate. It is a date field, but we want it to be a string on the CSV file, so we go up to FieldSequence and add another string field and call it TRANSDATE. Go ahead and map this to the TransDate field and bind it as well. Then click on Edit formula and you can use this to get the format: DATEFORMAT(@.TransDate,"MM/dd/yyyy") The Ms in the Month hav to be capital and the ds in the day and ys in the year format have to be lowercase.
The next field is BANKNEGINSTRECIPIENTNAME. Go back up to FieldSequence and add another string. Map and bind it to the BANKNEGINSTRECIPIENTNAME field. Then Edit the formula. We want to remove any commas. The formula should be:
REPLACE(@.BankNegInstRecipientName, ",", "", false)
The last field is the AMOUNTCUR. This is a Real data field but again, we want a string on the file. Go up to FieldSequence and add another string field. You can call it AMOUNTCUR. Map and Bind it to the AmountCur field in the positive pay model and then edit the formula. This formula will strip out the decimal and ensure that the dollars and cents are accounted for on the file:
REPLACE(NUMBERFORMAT(@.AmountCur,"F2", "en-us"),".","",FALSE)
Save your formula and go back and save the whole file formatting. You can mark your draft complete and export the XML format to save it somewhere safe. You can also use this to import into other environments.
