Hi Kathi, welcome to the forum!
You've hit it on the head: you're seeing zeros in the imported values because the lookup value couldn't be found (no match), and the null option is set to display zeros in your model. Go to Options, View... to see the literal which will be displayed for null values.
When your calculated net amount field tries to do a calculation on a null value, you're right again, it bombs. In fact, the calculation returns a null value, but you can't see that because the model is converting the null to a zero. This is one reason I like to display something like "*NULL*" instead of zero or blanks.
The solution is to take this into account, and use Monarch's IsNull function in your calculated field, similar to this example formula for a calculated Net field:
[font="courier"]If(IsNull(Payment),Charges,Charges-Payment)[/font][/quote]The Net amount will be equal to Charges if the Payment is null (not found), and the net amount will be Charges less Payment when a payment is found.
As to how to have a single payment amount imported when you have multiple charge lines for a specific individual, I cannot envision a solution for that now. I'll post back if I come up with something, but do invite other forum members to chime in.
Also, if I have multiple lines in my original table and I have a single line in my import for that person, it is importing onto each line. Is there a way to stop that? /b[/quote]Kathi, it sounds like you either need to summarise the multiple lines into a single line per person (if that is all you have available on the imported side) or look for an extended key for the look up - multiple fields perhaps such as person and transaction reference or account number for example - so that the imported value only matches one of the multiple lines.
The question that comes to my mind is whether the matches you are currently getting are accurate. If you get multiple lines imported that match multple lines for a person in your original table, how do they match? Luck perhaps? Or is there something else which makes the lookup work correctly? If so whatever that is needs to be applied to the single imported lines as well.
I may be wide of the mark on this one but I have found myself in similar situations before now so thought it best to raise a warning flag!
I think I've got a solution for you, based on Grant's comment on summarized records.
Since you didn't specify what your lookup field is, for arguement's sake let's say it's customer number.
Let's say you've got 3 charges on a particular account and a payment to import. With the current situation, the payment amount you'd end up would be three times the actual payment amount, right?
To get the proper Net amount, I propose you break this up into two distinct activities. First, create a model which gives you a summary in which you get total charges on one line per customer number. Export that summary to an Excel file. Now save this model, and close the report and the model.
The second step is to read in that new Excel file as a database source. To that data, you'll now import the payment data from the other file. This will give you the accurate Net amount. Obviously, save this model too.
Thanks for the ideas. I'll give them a shot. I've also looked at modifying the query that is creating the excel spreadsheet that I am importing the payment from. If I can get the appropriately modified, I may have another key to match the import on that will cut back on the problem.
I appreciate all the help! I've been reading the forum for ideas long before I joined. I knew you guys would be able to help!