InaPlex
»
InaPlex
»
Support
»
InforCRM - How to return TRUE or FALSE if record exists in database
Rank: Newbie
Groups: Registered
Joined: 7/17/2017(UTC) Posts: 3  Location: London Thanks: 1 times
|
Hi, I am trying to Change Data depending on whether the contact is already in the database or not. What I am doing is this: On the Change Data field, I am running the following Test Expression using dbselect(): Code:
SELECT CASE WHEN EXISTS (
SELECT *
FROM Table
WHERE EMAIL = ‘#Email’
)
THEN CAST('TRUE' AS BIT)
ELSE CAST('FALSE' AS BIT) END
When I click on “Test Expression” it returns True or False as expected, but when updating the preview or running the job, it says: Code:
An error was encountered when parsing an expression. Error details:
Tab: Change Data Column: Test Expression Row: 1
The expression dbselect("T", "SELECT CASE WHEN EXISTS (
SELECT *
FROM Table
WHERE EMAIL = '#Email'
)
THEN CAST('TRUE' AS BIT)
ELSE CAST('FALSE' AS BIT) END") does not give a True or False result
I guess that since I have put TRUE and FALSE between single quotes it is being sent as a String and Inaport might be expecting a Boolean. I have tried changing it to: THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) but got the same result. Maybe I am overworking this query, but I think this should work. Any advice?
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22
Was thanked: 2 time(s) in 2 post(s)
|
Hi Marcello
I think the issue is that the dbselect() will always return a string value, that tyou will then have to convert.
I suggest you modify to return teh 'TRUE' or 'FALSE', then test that:
dbselect() = 'TRUE' -- will result is a boolean true if the result of the dbselect() is TRUE
Regards
Support
|
 1 user thanked ipadmin for this useful post.
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 7/17/2017(UTC) Posts: 3 Location: London Thanks: 1 times
|
Ok, so as I mentioned in my first post, I may be overworking this query, so I have managed to do it in a shorter, simpler and more efficient version as below: Code:
dbselect(“T”, “SELECT COUNT(*) FROM Contact WHERE EMAIL IN (‘#Email’)”)=’1’
This SQL query returns the count of entries, as it is a requirement in our environment that emails must be unique, it will always return 1 or 0. Dbselect is now delivering TRUE or FALSE to Inaport as expected. Thank you ipadmin, your reply put me in the right direction. Edited by user Thursday, July 20, 2017 1:45:45 AM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 8/17/2015(UTC)
Posts: 22
Was thanked: 2 time(s) in 2 post(s)
|
|
|
|
|
|
|
| Users browsing this topic |
|
Guest (2)
|
InaPlex
»
InaPlex
»
Support
»
InforCRM - How to return TRUE or FALSE if record exists in database
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.