Here is my solution to this trap problem...
I would trap on the 'Currency Code', and I'm assuming that it will remain 3 alpha characters. So I used 3 consecutive alphas to trap. The only problem remaining is the dashes to the left of the USD amount. Header, Invoice, and Currency fields are char fields. Using this method, the 4th dash will give you grief when you verify the trapping. To resolve this, add a 1 character field on the 4th dash on the report. Name it anything. Save the template, go into the table, click on the field list, and hide that field.
Inv 2341 EUR 145,76
Inv 4524 EUR 56,89
Inv 5662 EUR 356,88
<---Problem USD 378,89
Inv 5624 EUR 45,00
Pete & Darren,
This is an interesting question. I am not sure if Darren's solution will give you the separate columns you are looking for, for foreign currency. I assume Pete wants his Monarch Table view to look like this, for example:
5662 EUR 145,76 USD 378,89[/font][/quote]To get the foreign currency data in separate columns in a single row, instead of in a separate row, you need to use a multiple line field.
1. Do a trap specifically on the letters: EUR (your Base Currency). Paint fields for the Invoice Number, Currency, and Amount for now. You should see this trap capture ("gray out") all the data in your report sample except for the line with USD.
2. Now (still in Report View) double click on your Currency field. This will open the Field Properties window. Click on the Advanced tab.
3. For the End Field On section of this screen, you want to click on the button that says something like: "Minimum Action Only: End When Template is Encountered" (this is what V5 and V6 will say; the correct selection for V7 for same result is "None of the Above.") When you click OK you should see the USD now "grayed out" (trapped) too.
4. Repeat step 3 for the Currency field. Important: convert this field into a Character field (not Numeric).
5. Now go into your Table view. Your table should look like this - now we see the effect of steps 3 and 4 on where Monarch "put" that extra foreign currency data:
2341 EUR 145,76
4524 EUR 56,89
5662 EUR USD 356,88 378,89
5624 EUR 45,00 /font[/quote]6. Notice in our fourth line of data (for which foreign currency data does exist) how Monarch put two row's worth of data in one record, in the Currency and Amount columns. Of course, we need to separate this data into their own columns.
Now, as any of the Voodoo Monarch session attendees at our User Conference attendees will tell you smile.gif[/img] , we will just use some calculated fields with some Monarch text string functions to accomplish this.
FIRST, AN IMPORTANT NOTE: Take a look at the fourth row of the above sample Monarch Table. Monarch V5 and V7 will read what looks like a space, as a space. Alas, V6 reads this space as a "return" character (known as ASCII character 10). So, V6 requires a slightly[/i] different set of forumlas. Read on...
(a) Create a calc. field for Base Currency as follows:
or, for V6: LSplit(Currency;2;Chr(10));1
This formula works whether there is Foreign Currency data or not.
(b) Create a calc. field for Foreign Currency as follows:
or for V6: LSplit(Currency;2;Chr(10);2)
Notice this field will be blank if no Foreign Currency data exists in the Currency field.
(c) Create a calc. field for Amount in Base Currency as follows;
Or for V6: Val(LSplit(LTrim(Amount);2;Chr(10);1))
--Removes any leading spaces in Amount field (LTRIM)
--Splits the data based on presence of a space (if there is one, we will pull out the text appearing BEFORE the space) (LSPLIT)
--Converts that piece of text we have split out into a numeric value (VAL)
(d) Create a similar calc. field for Amount in Foreign Currency (if any):
Or, for V6: Val(LSplit(LTrim(Amount);2;Chr(10);1))
--Same logic as the above formula, except we are working with text appearing AFTER the space
--If there is no space, that means there is no foreign currency amount within our Amount column, so this formula will correctly equal zero.
(e) Finally, hide your original Currency and Amount columns. Should be all set!
[size="1"][ May 30, 2003, 10:28 PM: Message edited by: Mike Urbonas ][/size]
I've had a similar problem, and here's how I dealt with it:
Make the Detail trap a one-line trap on "Inv". Trap the InventoryNumber, BaseCurrency, and AmountInBaseCurrency.
Make an Append trap of two lines, trapping on non-blank character (See NOTE below). On line 2, highlight to capture the same positions as you did for the fields in the one-line Detail trap. In this case it will trap the dashes, "USD" & the amount on line 2 of the append trap. I used the names "checkLine" to capture the string "----", "capForeignCurr" to trap "USD", and "capForeignCurrAmt" to capture "378,89".
Of course, this will also catch the next record as the second-line value for ALL entries, including those without foreign currency amounts, so I used calculated fields to take out the unwanted entries. I created a calculated field called "ForeignCurrency", and used the statement If(checkLine="--
",[capForeignCurr],"")[/b]; Similarly, the calculated field "ForeignCurrencyAmt" used If(CheckLine="--",[capForeignCurrAmt],0)[/b].
Then I hid "capForeignCurr" and "capForeignCurrAmt".
If you'd like I can e-mail the model I made based on your text file. It's a fairly simple way to take care of it.
NOTE: Do NOT capture in the Append using the same trap as the Detail ("Inv")- although this technically works for the Append, it seems to mess up the Detail lines being properly captured.
Here is another possibility. Assuming the dashes exist (or something like them)
Trap the detail lines using numerics for the Invoice number and 3 alpha for the currency code. Fields for Invoice Number (May be redundant later but a good check), Currency code if required and amount.
Create an append template trapping on "Inv" with fields for Invoice Number (as a check to the other one!) Currency Code and Amount. For the Currency Code and amount fields go to Options and set 'Start Field On' to "------" (or whatever)and end field to 'Minimum action'.
Should give the fields you want.
Alternatively Mike U's solution has a variant. Capture both currency code and value in a multi line field, then split it. Same sort of thing but gives the columns grouped as you want to see them.
Hope this is useful.
Can someone help with a small trap problem. I am trying to extract a table of receivables balances from an accounting system report that includes currency balances only when the currency is different from normal(EUR). The currency balance is printed in the report on a separate line only when this occurs. A simple example of this is shown below
Inv 2341 EUR 145,76
Inv 4524 EUR 56,89
Inv 5662 EUR 356,88
Inv 5624 EUR 45,00[/font][/quote]The columns in the table view should be Invoice number, Base Currency, Amount in Base Currency, Foreign Currency, Amount in F Currency.
Although the actual report is more complex than this I think I can solve it if I can resolve the simple case above.
Can anyone help me , please /b[/quote]
Just to mention (steal!!) the option Grant suggested. It's the preceding string. This will give the table with seperate columns and no need for calculations
5662 356.88 USD 378.89
/font[/quote]Trap the detail (ignoring the USD line)in the normal way.
To get the foreign currency, select the detail line again and use the same trap that only selects local currency lines. (ie the wrong lines are highlighted at this stage).
Highlight the currency and value fields, click on each and go to the advanced tab. Use a preceding string of "-" (without the quotes)
I don't think you will need to change the end field action.
That should do it.
[size="1"][ June 12, 2003, 05:29 AM: Message edited by: Steve Caiels ][/size]