HI,

I've actually been using FM Pro since the 80's, but only in a limited way. never became a Jedi, so to speak.

Anyway, its tax time, and I downloaded my csv file of all my Paypal purchases and income.

Unfortunately, the brilliant minds at ebay (paypal's new owners) didn't put the "Paid" and "received" amounts in two different columns. What do they give me? One column of payments and receivables, the payments showing a "minus" to let me know that a payment was made. How nice.

However, I uploaded this csv file into Filemaker 5.5 ( i guess i better upgrade cuz 5.5 wouldn't import a csv file).

For my taxes, i'd like to show what is INCOME in this column and what i payed out, which is only indicated by a minus (-) sign.

How can i move the Minus signs (payments), out of this field and put into their separate own field. THat way I can have payments in one field, and income in another. It will make my summaries oh so much easier.

Anyone have any ideas?

In summary: I have a list of dollar amounts, some with minuses (payouts) and some without minus signs). I'd like to be able to quickly remove the "minus" numbers into a different field I've made called "payments".

Thanks for you help.

[email protected]

You have several choices for download, and the csv is just a comma separated text that can be imported or openned directly in FileMaker.

You can either parse out the data with the minuses in FileMaker, or deal with in a Text Editor such as BBEdit, or TextWrangler. Most of the time I recommend the Text Editor Route, but in this case the file could be parsed easy enough with a simple loop script.

Once you have the data in FileMaker, you can either create a Field, or use the one that was created with no data due to the last set of "".

The script would look like this:

Go to Record/Request/Page

      [ First ]

Loop

    If [ Left ( Download Converted::Amount ; 1 ) = "-" ]

        Insert Calculated Result [ Download Converted::f10; Download Converted::Amount ] [ Select ]

        Clear [ Download Converted::Amount ] [ Select ]

   End If

   Go to Record/Request/Page [ Next; Exit after last ]

End Loop

HTH

Lee

It sounds like you will do this yearly. In addition to Lee's outstanding answer ...

You can import directly into a holding field (number). Have two fields Payments and Income (both standard number fields). Set Income Auto-Enter to: If (holding > 0 , holding , "" ). Set Payments to Auto-Enter of: If (holding < 0 , holding , "" ).

When you import, select 'File Type' of Comma Separated Text. FM will certainly handle csv. Map the Amount from PayPal to the holding field. And be SURE to click the radio to perform Auto-Enter operation. Then create a script with Import script-step and when you exit, choose REPLACE to store the new import mapping within your script. Then you'll be ready for next year.

One of those two fields should be chosen to hold a value if 0? If so, adjust one of the them to include greater than or equal to (or less than or equal to). You can leave the holding field contents alone as a audit-type trail of your import.