Post

2 followers Follow
0
Avatar

How to use currency field in CZQL where condition

Hi Guys,

Do you know how to use currency fields in where condition in a CZQL statement ? 

I want to search an entity like below :

select ExternalID from RegularResourceLink
where C_ActualCostRatewithOH = 0


But "C_ActualCostRatewithOH" is a currency field and querying like this returning an empty set.

Is there any helper function for currency types to search acc. to value or currency fields of a currency property.

I want to search an entity like below :

where C_ActualCostRatewithOH.value = 0 and C_ActualCostRatewithOH.currency = 'GBP'

Mustafa Kipergil Answered

Official comment

Avatar

Hi Mustafa,

Hopefully these guidelines will be of use:

1. Currency values via API can only be filtered in the base currency. You can do additional filtering by CostCurrencyType or RevenueCurrencyType. You could also do application side filtering. 

2. Although they are displayed as rounded numbers on the screen, currency and duration fields are often stored as longer decimal values so you can't run a simple "=" or "<>" filter on them. Instead, the way to query them is by sending a range filter .

3. Here's an example for retrieving all projects with PlannedBudget=456: 

"select Name,PlannedBudget FROM Project WHERE PlannedBudget >= 455.995 AND PlannedBudget <= 456.005"

4. You can use "=" or "<>" on Numeric and Percentage fields. 

I hope this helps,

Elad

 

 

Elad Franklin

Please sign in to leave a comment.

1 comment