laker_42 Posted February 25, 2005 Share Posted February 25, 2005 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 Link to comment Share on other sites More sharing options...
-Queue- Posted February 25, 2005 Share Posted February 25, 2005 It seems like it should work. Can you post the file? Link to comment Share on other sites More sharing options...
laker_42 Posted February 28, 2005 Author Share Posted February 28, 2005 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 Link to comment Share on other sites More sharing options...
laker_42 Posted February 28, 2005 Author Share Posted February 28, 2005 Here is the last file, it wouldn't fit with the first post. 2ndDb.zip Link to comment Share on other sites More sharing options...
bikergeek Posted February 28, 2005 Share Posted February 28, 2005 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". Link to comment Share on other sites More sharing options...
laker_42 Posted February 28, 2005 Author Share Posted February 28, 2005 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 Link to comment Share on other sites More sharing options...
laker_42 Posted March 1, 2005 Author Share Posted March 1, 2005 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6995 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now