Resource Import Data Filter and Concatenating fields in Connector Solution 7
Dear All,
I am importing from an Excel Spreadsheet where there is a "DELETED" field and I need to filter out these records. If I put in:
[DELETED] <> 'DELETE'
then I only get records where the field has something in it (and it is not "DELETED"), rather than all records where the field is not "DELETED". I guess that there is a problem with NULLS? How can I get it to do what I want it to do?
Also, in the same spreadsheet, I have Address1, Address2, Address3 fields, but just 1 Address field in the Dataclass. How do I use an Expression to append the three Addresses?
I have tried:
NULLIF([Address1], '') + CHAR(13) + CHAR(10) + NULLIF([Address2], '') + CHAR(13) + CHAR(10) + NULLIF([Address3], '')
And I have tried:
[Address1] & vbCrLf & [Address2] & vbCrLf & [Address3]
Where am I going wrong, (again?)
Thanks in advance.
Regards,
QuietLeni
Comments
Partial Answer
Dear all,
I feel embarrassed - I have solved the first one. I have changed the filter to show:
[DELETED] <> 'DELETE' OR [DELETED] IS NULL
This then showed all of the correct records.
However, I am still having problems with the Address field. I have worked out that the correct syntax is:
NULLIF([Address1], '') + ' ' + NULLIF([Address2], '') + ' ' + NULLIF([Address3], '')
but I cannot get the separator to be a carriage return to get the field onto the next line (such as CRLF or CHAR(13) + CHAR(10).
Anyone know the answer?
I guess, though, the BIGGER QUESTION is: What are the acceptable values in the Expression field for Connector Solution?
Regards,
QuietLeni
I stand by the accuracy of my Inventory of the organisation. Don't blame me for the picture that it presents...
The documentation says...
Dear All,
The documentation for Connector Solution says that I should be able to use a "\r" for a cariage return or a "\n" for a new line, but neither of these work.
Does anyone know the answer?
Thanks in Advance,
QuietLeni
I stand by the accuracy of my Inventory of the organisation. Don't blame me for the picture that it presents...
In Search of Same Information
QuietLeni,
I see that no one responded. Did you figure out how to add a new line or carriage return via the Connector Solution?
I opened a ticket with tech support, but they seem to be having trouble figuring this out as well.
Thanks,
Janelle
No, I didn't... ...but...
Janelle,
No, I didn't - I ended up saying to my colleague that I could not put in a CR and concatenated the fields together, so the answer is still out there (with technical support)...
QuietLeni
I stand by the accuracy of my Inventory of the organisation. Don't blame me for the picture that it presents...
Would you like to reply?
Login or Register to post your comment.