Why not just place a copy of the Currency field to the right of the payment field?
The problem with that is when I make a trailing grand summary, I need to show $ or € or JPY "total invoiced", "total paid" and "total balance" separetely according to currency. So if for one product $ is chosen and € for another, at the trailing grand summary "total's" of each currency will have to be separated.
If you can show me a way making this possible, that would be great!
If you have more than one currency, you cannot have a trailing grand summary like this, but you can have sub summaries for each if you group them by currency type by sorting. Then the currency field can be placed in the sub summary part.
If you want a "recap" at the bottom of the report with separate totals for each, you will need to do a lot more than just display the correct currency, you also need a method for computing each sub total that allows you to include in the trailing grand summary. Are you able to get separate totals for each currency type in your report? If so, what method did you use. (We can probably use the same method to produce a calculation field to display the currency symbol.)
Well I didn't prepare a report yet but I was hoping there was a way to summarize each currency type separately rather than sorting by currency. If there is no way of getting total's of each currency separately after data entry, I will write under this post when I make a report.
But the way you told me about sorting with currency type might work partially for me now, since I will still get a separation by currency type.
It's possible to get what you want, but you'll have to do some more design work to make it happen.
Here's one way you can use if you have FileMaker 11:
Go to Manage | Database | Relationships, click the table on which you are basing this report, then click the duplicate button (two green plus signs), to make a second occurrence of this table.
Link these two occurrences like this:
Yourtable::anyField X Yourtable 2::anyfield
Put a one row portal for each currency type to YourTable 2 on your trailing grand summary. Give each portal a portal filter filtering by a specified currency type. Put a summary field from YourTable 2 inside the portal to display the total funds for that currency type. Since you have a portal for each currency type, you can add layout text next to each to display the appropriate currency symols.
Here's a typical portal filter expression that whould only show the total dollars:
YourTable 2::currency = "$"
You can make your portal filter more sophisticated in order to total up funds only for a sub set of your entire table. You could, for example, set it up so that the user can specifiy a date range and the portal filter can filter out all records not in the specified date range.
This is not the only option, you can also set up a related table where you have one record for each currency type and use it in relationships and a portal to put this recap into your sub summary part.
I will try what you wrote tomorrow Phil thanks. I just have another quick question for you. When I do sort first for currency type (according to dropbox valuelist) then inventory ID "JPY" comes up separately, as I wanted, but $ and € totals are together. I don't know how it did separate JPY and combined $ with €. Any suggestions?
Is this a field of type text or number? Don't quite see how that would keep this from working for dollars and Euros but still separate out JPY (Japanese Yen?), but only thing that comes to mind here. It should be of type text.
I couldn't think of another problem as well until you wrote. I think I figured out. I don't know if this is the problem but, $ and € were figures but when I wrote them as DOLLAR and EURO in the value list, it seems to be working for now :)
If, on the storage tab in field options for this field, you change the default language to Unicode, it sorts as it should when you use the symbols for dollars and euros.
Thanks! And for the report, I will get back to you tomorrow!