Formulas are getting more complicated ... trying to use varnumber stat

[LadyAmpherion]

Part II: I now have my Actual Qty Received or the Zero Value using Katy44 if is Null(RCVDQTY) ... et cetera

The next part is similar but a little more complicated ...
when any amount is received, the RCVDCOST Value gets updated from the Receipt against the Purchase ORder; however if there are ZERO receipts then there is ZERO RCVDCOST.

The Value that I need to pull is on the PODETAIL ... and using the following Scenerio (thanks to Midearth):

numbervar x:=ACTCOST;

if ACTCOST > 0 then x else PODETAILPRICE

I actually get the Correct Value, BUT I Lose some line items and other line items get Doubled ... and there appears to be no pattern that I can ascertain, as some of the single entries remain as single entries.

*** of course, should there not be a PO Value, then I would have to pull the Budget Value .... which is in the same table as the RCVD Value ... so I'm really looking for the correct way to write

X = RCVD Value unless zero, then X = PO Value unless zero, then X = Budget Cost ... [RCVD Value and Budget Cost are in the Job Details Table, PO Value is in the PO Details Table]

I'm sorry to be so ignorant ... and welcome all of your assistance. Thanks again for any help that I may receive!

 

[synapsevampire]

The formula:

numbervar x:=ACTCOST;
if ACTCOST > 0 then
x
else
PODETAILPRICE

is the same as:

if ACTCOST > 0 then
ACTCOST
else
PODETAILPRICE

You've simply added an extra variable.

What you're probably experiencing is called row inflation, which means that you're gtting extra rows based on your table joins.

I would suggest speaking with the DBA about how to get the proper data, or use some means of limiting the rows per your requirements, which you haven't shared here.

Often times you can limit rows based on some date criteria, such as a Maximum to retrieve only the latest row per entity.

Example

Group by the PO

In the Report->Edit Selection Formula->Group place somethin like

{table.PODATE} = maximum({table.PODATE},{table.PO})

This will return only the latest row per PO.

Otherwise you'll want to share specifics in posts rather than text descriptions:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[LadyAmpherion]

Opps ... Crystal Version (10) Pulling Data from EziJobz (Build 97)

the PO Details is a One to One Correlation to the Job Details .... as it it the Purchase Order Detail for the Line Item in the Job Order.

 

[LadyAmpherion]

My End Goal is a Budget Based Report where we compare our Sales Price (as sold) to the Budget Costs (as budgeted) to our Total Actual Costs, which include money spent but not realized and estimates of money to be spent.

Project XYZ (Group)

Job Order for Assembly X

Job Order for Assembly Y

Job Order for Lot of Purchased Parts

Sales Price
(Qty) (Unit Price) (Total Sales Price)


Budgeted Costs
(Unit Cost) (Total Budgeted Costs)

Committed Costs (Received + Unreceived + Estimate Remain)

If there is any amount received, then I have a value that I can grab and use for both the received and the unreceived amounts. When there is no value yet for the received section, then I must grab the value from the Purchase Order Detail (one to one correlation for Purchase Order to Job Detail Line Item, but One to Many for PO to PO Receipt) ... if the Purchase Order Detail has no value, then we would want to pull the value from the Budgeted Cost ...

Is this more of what you were looking for?

Thanks again

 

[LadyAmpherion]

I cannot rationalize the why ... but I think I have found the pattern for the duplicate numbers ...

if I purchase one line item on one Purchase Order then, then the values print once ...

if there is no purchase order for the material, then there is no values (the whole line of data is gone) ...

if there are multiple line items (e.g. 2) on ONE purchase order, then it prints the values twice ...

so I know now the "WHY" in an abstract but I don't know the solve. ...

Thanks again

 

[LadyAmpherion]

Here is my current Formula:

if {JOBDETL.ACTCOST} > 0

then {JOBDETL.ACTCOST}

else

if {PODETL.PRICE} > 0

then {PODETL.PRICE}

else {JOBDETL.UNITCOST}

***************************
I have changed the links and now have the PO Detail Linked Directly to the JO Detail and now I only the ACTCOST Column again ... I am not pulling the PODETL.PRICE nor the JOBDETL.UNITCOST

***sigh***