wayy2be
asked on
How to display a result only if two fields meet a certain criteria
Hi,
I am trying to create a formula that will only display results if one field is not equal to null and the other field is = to shipped. I am not quite sure on how to do this. How can I accomplish this?
Thanks
I am trying to create a formula that will only display results if one field is not equal to null and the other field is = to shipped. I am not quite sure on how to do this. How can I accomplish this?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this
Open the report
Click REPORT --> SELECTION FORMULAS --> RECORD
Enter the formula
IsNull({Table.field1}) AND {Table.field2} = 'Shipped'
Click SAVE AND CLOSE
Run the reort
mlmcc
Open the report
Click REPORT --> SELECTION FORMULAS --> RECORD
Enter the formula
IsNull({Table.field1}) AND {Table.field2} = 'Shipped'
Click SAVE AND CLOSE
Run the reort
mlmcc
ASKER
Thanks for your reply. Actually I misstated the requirement, please see above. Thanks.
That is what the record selection is for.
How do you determine membership in VIP club?
How do you know they ordered a product?
mlmcc
How do you determine membership in VIP club?
How do you know they ordered a product?
mlmcc
ASKER
The table has a field for both and we would need to set it to be = to VIP and product X.
{VipField} = 'VIP' and {ProductField} = 'ProductName'
mlmcc
mlmcc
ASKER
So just the following will list all records with both fields equal to VIP and Product Name?
{VipField} = 'VIP' and {ProductField} = 'ProductName'
{VipField} = 'VIP' and {ProductField} = 'ProductName'
It should. Be sure to use your database field names in place of VipField and ProductField
mlmcc
mlmcc
ASKER
Sorry I was away. No this does not work, returns no data.
mlmcc's suggestion sounds good, but we don't really know anything about your data. For one thing, you mentioned customers and products -- Are the customer and product fields that you're checking both in the same table, or separate tables? If they're in separate tables, how are they linked?
If mlmcc's suggestion didn't give you any records, what happens if you try each part separately?
Try {field1} = "VIP" and see what you get, then try {field2} = "productX" and see what you get. Obviously, replace the field names and values with your field names, and the values you're actually looking for.
You are entering those tests in the record selection formula, correct? For example, Report > Selection Formulas > Record.
James
If mlmcc's suggestion didn't give you any records, what happens if you try each part separately?
Try {field1} = "VIP" and see what you get, then try {field2} = "productX" and see what you get. Obviously, replace the field names and values with your field names, and the values you're actually looking for.
You are entering those tests in the record selection formula, correct? For example, Report > Selection Formulas > Record.
James
ASKER
The fields are in the same table. I may not be explaining correctly. What I need is that if a field has VIP and another field has Product X then display those records that meet that criteria. For example VIP and Product B, no data is displayed.
Are they in the same record?
mlmcc
mlmcc
If the two values that you're looking for are in the same record, then it really should be just as simple as the formulas that mlmcc and you posted earlier. You should be able to just go to Report > Selection Formulas > Record and enter a formula like this:
{field1} = "some value" and {field2} = "some other value"
If the fields could be null, you may need to test for that, but apart from that, it should be just that simple. Which is why we keep asking these other questions (how many tables, are the values in the same record, and so on). What you described seems pretty basic, so if it's not working, we're missing something.
James
{field1} = "some value" and {field2} = "some other value"
If the fields could be null, you may need to test for that, but apart from that, it should be just that simple. Which is why we keep asking these other questions (how many tables, are the values in the same record, and so on). What you described seems pretty basic, so if it's not working, we're missing something.
James
ASKER
Sorry for the delay. I will test it again and report back on Monday. Thanks
ASKER