Forums

Forum Home

Support

Foxfire! 6.0x Support

Pass Foxfire &q...

Pass Foxfire "filter value" into SQL data item?

8/28/2007 7:35:25 AM

uberstein

12 posts

Pass Foxfire "filter value" into SQL data item?

Here's (possibly) an easy one.
How do I pass a foxfire "filter value" into a Data Item expression (which is written in T-SQL)?

I am aware of the flt_val() variable, and I have used it successfully in some layouts, but I am not sure how to pass one of these values into an SQL expression in a Data Item.

I have created "Bucket" Data Items for an AR report using todays date (getdate()) in order to determine which bucket to place a balance in. the expression is currently this:
CASE WHEN DATEDIFF(dd ,ACCTSTATUS.FROM_,GETDATE()) <=30 AND ACCTSTATUS.DELFLAG = 0
THEN ACCTSTATUS.OBALANCE
ELSE 0.00
END

This works beautifully for all of my bucket data items.
I would like it to instead use the filter value (which in this case is a datetime entered by the user at runtime), like this:
CASE WHEN DATEDIFF(dd ,ACCTSTATUS.FROM_,flt_val(2)) <=30 AND ACCTSTATUS.DELFLAG = 0
THEN ACCTSTATUS.OBALANCE
ELSE 0.00
END

Thanks for any help!

8/29/2007 8:04:03 AM

AndrewM

149 posts

www.aksel.com

Re: Pass Foxfire "filter value" into SQL data item?

Good question. I know it's possible - but I'm not sure on the best approach to take on it. Let me check to see how the filter is being generated to see if you can modify it directly there.

8/29/2007 8:43:39 AM

uberstein

12 posts

Re: Pass Foxfire "filter value" into SQL data item?

Ok thanks. The real issue is being able to pass that value to the SQL server via the Data Item Expression, as above. Unfortunately the second example

CASE WHEN DATEDIFF(dd ,ACCTSTATUS.FROM_,flt_val(2)) <=30 AND ACCTSTATUS.DELFLAG = 0
THEN ACCTSTATUS.OBALANCE
ELSE 0.00
END

(and the seemingly obvious way to do it) causes an error box to appear telling me that it doesn't know what to do with flt_val, specifically the error message is 'flt_val' is not a recognized built-in function name.'

Perhaps it is the necessity of adding the parentheses-enclosed (2) to denote which filter line to take the value from which is making SQL server think it's a function rather than a variable in an array.

Hmmm...

8/29/2007 9:07:53 AM

uberstein

12 posts

Re: Pass Foxfire "filter value" into SQL data item? (SOLVED)

Aha. After a bit more googling, I found it.

Adding a ? before a foxpro variable name allows SQL server to see it as a variable and use it. So simply changing flt_val(2)
to
?flt_val(2)

in the expression solved the problem.

Here is the final expression which allows a filter condition variable to work in an SQL expression:

CASE WHEN DATEDIFF(dd ,ACCTSTATUS.FROM_,?flt_val(2)) <=30 AND ACCTSTATUS.DELFLAG = 0
THEN ACCTSTATUS.OBALANCE
ELSE 0.00
END

This works perfectly.

In any case, thanks for the reply, Andrew!

8/30/2007 9:11:29 AM

AndrewM

149 posts

www.aksel.com

Re: Pass Foxfire "filter value" into SQL data item? (SOLVED)

Glad to hear that worked out. I'll make a note of it in case anyone else asks about it.

Thanks for letting us know and thanks for using Foxfire!

Page 1 of 1

Support

Foxfire! 6.0x Support

Pass Foxfire &q...