There is uncertainty that you need to address, and make sure you handle these the same way in Excel for your lookup. Athese all a fixed length, or are leading zeros dropped?
If every policy number is 7 positions and the amount is always 4 positions, then I would just handle them as character fields (in the template) and concatenate them with the + operator.
If you need leading zeros droped, then handle them as numeric in the template, and in the calculated field, convert them to a character field (str function), then concatenate them.
Then, you need to determine if you need a string or a numeric field to link back to your spreadsheet. Since you used character for your Monarch fields, a string will work as is. To use numeric, use the Val function.
In monarch I have used the Val(Polnum) calculated field to get rid of the leading zero's, the problem is I need the "type" of both to be numeric because I need to find the total in the summary, If I change to character I can't do that...
In excel my format is "number" to 2 decimal places here is a better example of what I need to do
Here is what it looks like in Monarch
Pol num Dolar amt
What I did in excel to combine them - I need monarch to do the same
Thanks for all the help so far....
/b[/quote]I'm with Joey on this and would observe that the Excel combination by dropping the decimal places ONLY if the values are zero does not make things any easier at all! (I assume that is how the combination is working as shown above). I'm not even sure that you could guarantee a unique string although to be fair the chances of duplication would likely be extremely small.
I think you may need to include the decimal place values (and hope you don't get recurring amounts in the same PolNum if you are looking for unique links).
In theory the PolNum is likely to be a Character field in essence rather than numeric so the link should be based on character fields in my opinion. However to get the linke, if both field start as numeric, you could simply multiply the numeric value of the PolNum field by 10 to the power of the number of whole value characters in the Amount field and than add the amount.
167632 10,000.00[/font][/quote]1237.55 would require 4 zeros so multiply 18998 by 10,000 to get 189980000.00 . The add 1237.55.
10000.00 would require 5 zeros so multiply 167632 by 100,000 to get 16763200000 and then add 10000.00.
Then convert to be a character field.
There are variations to the this approach of course but in principle what ever you need needs to be simple to maintain and full proof against the vagaries of numbers in databases.