Writing a report that deals with a "for each" clause?

[Former Member]

Hello all beginner here,

I have something a little complicated I think that may or may not be able to  run in a crystal report.

I have this table called PURCHASE_QUANTITY_PRICE\ that holds two values I need, PURCHASE_QUANTITY_PRICE\.VALID_FROM and PURCHASE_QUANTITY_PRICE\.VALID_UNTIL. These two values I pull to dictate whether a price has a valid date or not.

I need to find something like this PURCHASE_QUANTITY_PRICE\.VALID_UNTIL<= CurrentDate for each INVENTORY_PART\.PART_NO that does not have a PURCHASE_QUANTITY_PRICE\.VALID_UNTIL >= Current Date.

So currently my code looks like this:

{PURCHASE_QUANTITY_PRICE\.VALID_FROM} <= CurrentDate and

{PURCHASE_PART_SUPPLIER\.PRIMARY_VENDOR_DB} = "Y" and

{PURCHASE_QUANTITY_PRICE\.VALID_UNTIL} < currentdate and

{PURCHASE_PART_SUPPLIER\.PRIMARY_VENDOR} = "Primary supplier"

This code currently shows all INVALID PRICES for all INVENTORY_PART\.PART_NO

I am not really sure any other workarounds or ways to think about the logic differently.

Thanks for the help. Any questions just ask!

-Ajax

[abhilash_kumar · Accepted solution]

Hi Ajax,

I'm not sure I understand this:

PURCHASE_QUANTITY_PRICE\.VALID_UNTIL<= CurrentDate for each INVENTORY_PART\.PART_NO that does not have aPURCHASE_QUANTITY_PRICE\.VALID_UNTIL >= Current Date

Do you have an example dataset with the expected output?

P.S: The current record selection formula looks right to me though.

-Abhilash

[Former Member · Accepted solution]

No, I don't have an example dataset with the expected output. So I'll try and explain it better.

We have in our records something that looks like this

This is what I get when I have no restrictions.

When I restrict to:

{PURCHASE_QUANTITY_PRICE\.VALID_FROM} <= CurrentDate and

{PURCHASE_QUANTITY_PRICE\.VALID_UNTIL} >= CurrentDate

I get zero results because the last known valid date was from 2014.

For this report I need to find for each part number:

• Have a VALID_UNTIL <= CurrentDate
• Does not have VALID_UNTIL >=CurrentDate

My code:

{PURCHASE_QUANTITY_PRICE\.VALID_FROM} <= CurrentDate and

{PURCHASE_QUANTITY_PRICE\.VALID_UNTIL} >= CurrentDate

currently just shows those parts that are valid.

I want to show any parts that do not have a valid_until that is current, but I do want to show parts that have a valid_until date that is expired. Expired being a date that is in the past from the current date. Some parts will show up more than once and have multiple expired dates but have a current date - I don't care about those.

So not only is this a coding puzzle but it is also an English majors nightmare.

[vinita_kasliwal · Accepted solution]

Hi Ajax

I think what Devanshi posted below would work ..

Since you would want to show those documents that are not expired ?

just put restriction which says check if valid_until column > Current date if yes show else Hide

Maybe I am not understanding the problem if yes do explain a bit more !

and yes the nightmare part was funny

Regards

Vinita

[abhilash_kumar · Accepted solution]

Hi Ajax,

Please see if this works:

1) Modify the selection formula to:

{PURCHASE_QUANTITY_PRICE\.VALID_FROM} <= CurrentDate and

{PURCHASE_PART_SUPPLIER\.PRIMARY_VENDOR_DB} = "Y" and

{PURCHASE_PART_SUPPLIER\.PRIMARY_VENDOR} = "Primary supplier"

2) Insert a group on the Part No field. Suppress the Group Header and Group Footer if you don't need to display it

3) Create a formula (@Current) with this code:

If {Valid_Until} >= currentdate then 1

4) Create a formula (@Expired) with this code:

If {Valid_Until} < currentdate then 1

5) Go to the Report Menu > Selection Formulas > Group and add this code:

Sum({@Expired}, {Part_No_Group}) >= 1

AND

Sum({@Current}, {Part_No_Group}) = 0

P.S: I would prefer to do this in SQL for performance reasons though. You can then use the SQL as the datasource for the report.

-Abhilash

[Former Member · Accepted solution]

Doing this in SQL is an option but for now just getting the code correct is priority.

When I used your code the only thing that doesn't seem to have went correctly is this:

I'm not really sure what you're doing so I can't really troubleshoot this matter. Thank you.

[abhilash_kumar · Accepted solution]

Remove the text 'GroupName'. It should just be:

Sum({@Expired}, {INVENTORY_PART\.PART_NO}) >= 1

AND

Sum({@Current}, {INVENTORY_PART\.PART_NO}) = 0

-Abhilash

[Former Member · Accepted solution]

Yes, this did exactly what I needed it to. Thank you.

[former_member336330]

{PURCHASE_QUANTITY_PRICE\.VALID_UNTIL} < CurrentDate

will show expired parts

[Former Member]

Yes, that would work. But for each part they have 3 or 4 dates that are before the current date. So yes, that will show expired parts but we don't want to see those 3 or 4 expired dates. We just want to see what is expired or expiring. Without seeing those that have expired in the past.