I'm using CR XI R2.

I have a report that is run once a year for specific products used in the last year.  It runs perfectly if the product was cycle counted at the end of the previous year.

The starting inventory is a result of a formula that looks for the Cycle Count within 5 days prior and 5 days after the start date of the range specified in the parameters.  This is my formula as it currently exists.  "C" means the transaction was a Cycle Count:

If
{Transaction_History.TNXCDE_15} = "C"
and
{Transaction_History.TNXDTE_15} in {?DateRangeStart}-5 to {?DateRangeStart}+5
then
{Transaction_History.TNXQTY_15}

However, some products may not get cycle counted at the end of the year so when the report is run at the end of the following year, the data is incorrect.  Since the product has no Cycle Count, it inserts 0.00 as the starting inventory which then throws off all the rest of my formulas.

To remedy this, I need a formula to select both the absolute first Running Total record and the absolute first Transaction record that fall within the entered date range parameters and add those records together.  I can then use this as my starting inventory.

Then I can change my formula above to:

If
{Transaction_History.TNXCDE_15} = "C"
and
{Transaction_History.TNXDTE_15} in {?DateRangeStart}-5 to {?DateRangeStart}+5
then
{Transaction_History.TNXQTY_15}
else
{@Unknown_Formula}

Can anybody help me?


Edit:
I also just realized I need a formula to select the absolute last Running Total record that falls within the entered date range parameter for my Actual Ending Inventory.

Edited by GrisCorp - 10 Jun 2015 at 6:55am