I have 4 tables, names, productsales, productsalelineitems, contributionlineitems. names -> productsales on custID. ProductSales-> lineitem tables on Invoice#, name-> lineitems tables on custID.

All sale items go into the productsalelineitems table. All donations go into the contributionlineitems. I want to set a flag that shows me whether a customer is a donor or not. I created a calculated field, DonorFlag, and used the following calculation: If(Not IsEmpty(contributionlineitems);"Yes";"No"). This seems to work for some but it is not working consistently. Should I be using another technique to get this info? Is it working inconsistently because I have a 1 to many relationship between my names & lineitems tables? Any help would be appreciated.

Thanks!

John

It seems like it should work. Can you post the file?

Here are the files. For the names file use customerservice for username and password. I believe everything else uses admin with blank password. I have tried several different things but I can't get this to work.

Thanks for the help!

John

PS I didn't create this database so please don't judge me on the design. It is a little ugly. I am creating an interface file and using these as the data files.

DBs.zip

Here is the last file, it wouldn't fit with the first post.

2ndDb.zip

A simpler approach might be to format that calc field as follows:

DonorFlag = Count(contributionlineitems::amount)

Format number of DonorFlag as boolean, with true values (>0) reported as "Yes" and false values (0) reported as "No".

This is working for my values of yes. At least it seems to be. I may have to have it auto fill for the no values. Right now the records that should be no are just blank. Its probably not that big of a deal. It all depends on how our marketing team would like to see it.

Thanks for the help!

John

I figured out what was wrong with my inital posting. My relationships were not setup correctly. I had the account table related to the lineitems table via account#. That doesn't make sense. I changed the account table to relate to the productsales table. The sales table then relates to the lineitems tables. The extra relationship was getting in the way. Thus, the irradict behavior.

I can't believe I didn't see that earlier.

John