Formula Review

[stcholly]

Crystal 10/Timberline Database
I need help with the below (very lengthy) formula. I have an instance where my "01" number may or may not have a committed cost entry. For instance, cost code "01-710" has a commitment on one job, in which I want that amount to show on the report. On another job, "01-710" may not have a commitment, in which case, I need the report to show the (@Original Budget) amount.

The way the below formula is currently written, instances where there is no commitment entered, it is giving me the correct number, but in instances where a commitment exists, it is adding the commitment amount to the original budget.

I've re-worded this formula every which way I can, and still get the same results. I know basic crystal design, so I'm very limited to troubleshooting.

Any help is GREATLY appreciated!

if{CURRENT_JCT_TRANSACTION.Transaction_Date} <= {?Cutoff Date} and
({CURRENT_JCT_TRANSACTION.Transaction_Type} = "Committed cost" or {CURRENT_JCT_TRANSACTION.Transaction_Type}= "Misc worksheet 1" )

then {CURRENT_JCT_TRANSACTION.Amount}

else if {@Commitment Balance} = 0 and
left(totext({CURRENT_JCT_TRANSACTION.Cost_Code}),2) = "01"
then {@Original Budget}

else if {@Commitment Balance} <> 0 and
left(totext({CURRENT_JCT_TRANSACTION.Cost_Code}),2) = "01"
then {@Original Budget}

Other formulas:
@Commitment Balance =
If {CURRENT_JCT_TRANSACTION.Transaction_Type} in ["Committed cost","Aprvd cmmtt cst chng"] and {CURRENT_JCT_TRANSACTION.Transaction_Date} <= {?Cutoff Date}
then {CURRENT_JCT_TRANSACTION.Amount}
@Original Budget =
IF {CURRENT_JCT_TRANSACTION.Transaction_Type} = "Original estimate"
Then {CURRENT_JCT_TRANSACTION.Amount}

 

[dgillz]

Test your fields for null values, I suspect {CURRENT_JCT_TRANSACTION.Cost_Code}

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[stcholly]

Thank you dgillz,

However, {CURRENT_JCT_TRANSACTION.Cost_Code} is not the null value.

The {CURRENT_JCT_TRANSACTION.Transaction_Type} = "Committed cost" could be null in certain instances. How do I test for a null value if it is not a field, but a formula? (Sorry, not very savvy in Crystal....yet.)

 

[dgillz]

I dont understand your formula:

{CURRENT_JCT_TRANSACTION.Transaction_Type} = "Committed cost"

You cannot set a database field equal to a certain value in crystal...


if isnull({CURRENT_JCT_TRANSACTION.Transaction_Type}) then <<do this>> else ....

Be sure to make your null check the very first line of your code.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[stcholly]

dgillz - I appreciate your time (and patience) today. I'll try to explain the situation better...

With Timberline, when you enter a job cost, it creates a transaction record. The transaction 'type' depends on how the data was entered. For example, when you enter a commitment in Timberline, it posts to a job, by Cost Code. But there are many different Transaction Types ("AP Costs" = invoice entry in AP module, etc.). So, the only way to specify in the report to select the commitment entry (from a long list of transactions) is to say...

IF {CURRENT_JCT_TRANSACTION.Transaction_Type} = "Committed cost" then X - "committed cost" is selected from the Browse Data option.

So, I don't think the ISNULL will work in this situation. I didn't know if there was a clever way to work around it.

Thanks!

 

[dgillz]

I am at a total loss.

Please post sample data (not your existing report but what your raw data looks like), table and field names, table relationships and desired results.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[stcholly]

Here is a sample of a "transaction log"...

Cat TranDate Tranaction Type Amt

06-s-074 Our Job-Palm Beach, FL
01-710 Cost Code Description
12-31-06 Percent Complete 100.00

M 12-31-06 JC Cost 53.73
M 12-31-06 JC Cost 38.47
M 12-31-06 JC Cost 14.29
M 12-31-06 JC Cost 42.19

SUB 10-09-06 Original Estimate 3,097.00
SUB 12-07-06 Committed Cost 2,087.00
SUB 12-20-06 Aprvd cmmtt cst chng 456.00
SUB 12-21-06 AP Cost 2,800.00


So, I'm trying to select the $2,087.00 to pull into the report. The formula, as is, is pulling the 3,097 plus the 2,087. Note: Transaction Type is the only constant.