running total of items based on date range (?) 1

[VE]

I have a set number of rental items; I want to create a report that alerts when there are more items rented than are available.

Each record has from date, a to date and a qty rented. The qty owned is linked from another table.

What I want could look something like this (I’m not committed to this presentation):

Qty owned 2

Start date Return Date qty rented qty out
10/20/03 10/24/03 1 1
10/22/03 11/03/03 1 2
10/26/03 11/04/03 1 2
10/27/03 11/15/03 1 3 *

What I’m having trouble with is the “qty out” formula. I’m hoping it’s something really simple and I’ve just been staring at it too long

Thank you

 

[Trainingjason]

use the highlighting expert to say when 1 field is greater than the other, change the font colour

 

[VE]

thanx, but I need to figure out a formula to get to the 1 field that I'm checking to see if it's greater than the other field, which in my case is the qty rented field (I called it qty out accidentally in my last post, sorry to be confusing).

I've been trying something like:

if {TABLE.fromdate} in (previous({TABLE.fromdate}) to previous ({TABLE.todate})) then 1 else -1

then thinking that I can somehow get my answer by using a running total to add up the 1's and -1's and compare that to my qty owned field. But that's not working, one reason being that I need to go back more than one record.

I hope I'm making enough sense to describe what I'm trying to do. I also tried unsuccessfully to put it in a chart, what I want to show is the days where more than the quantity owned are scheduled to be in use for the same day.

thank you

 

[Trainingjason]

I am presuming that the quantity out is the currentdate. Hence

First Formula
if startdate>currentdatetime and enddate<currentdatetime then 1 else 0

RunningTotal on FirstFormula

Would that be a solution

 

[lbass]

Here is a method that works, at least with the limited testing I can do. Create two formulas:

{@reset} to be placed in the group header (if you have a group on item, for example):

whileprintingrecords;
stringvar qty := &quot;&quot;;
stringvar datein := &quot;&quot;;

{@totalout} to be placed in the details section:

whileprintingrecords;
stringvar qty := qty + totext({table.quantity},0,&quot;&quot +&quot;, &quot;;
stringvar array qtyx := split(qty,&quot;, &quot;
stringvar datein := datein +
totext(date({table.returndate}),&quot;MM/dd/yyyy&quot+&quot;, &quot;;
stringvar array datex := split(datein,&quot;, &quot;
numbervar totqty := 0;
numbervar counter := 1;
numbervar i := length(datein)/12;

for counter := 1 to i do(
if date(datex[counter]) >= date({table.startdate}) then
totqty := totqty + val(qtyx[counterx]) else totqty := 0);
totqty;

-LB

 

[VE]

I copy and pasted that, added my table.field names, but when I checked the second formula before closing it it stopped at the second to the last line between the

&quot;[&quot; and &quot;counterx&quot;

and said &quot;a number, currency amount, Boolean, date, time, date-time or string is expected here.&quot;

I really don't think I could have gotten that close on my own. Does &quot;counterx&quot; need to be declared? if so, what is it? Otherwise I'm lost.

Thank you

 

[lbass]

Sorry, that's a mistake--&quot;counterx&quot; should just be &quot;counter&quot;--I edited my test formula when posting and wasn't thorough, I guess.

-LB

 

[VE]

Wooowhooo!!! That did it!

Now that I have the number to compare to, I can make all the alerts I need .

Then I will work on understanding how and why this works... thank you very much