Good afternoon. I'm attempting to add both a phone number and separate sms number to each user's contact info in a database user source. I can add 1 or the other without any issue in the Contact Info Query field, but when trying to add both only the first one I enter shows up for each user. Is there something I might be overlooking? I've also tried adding the second number in the Extra Properties Query field with no luck. Below is what I thought might work in the Contact Info Query.
SELECT 'phone',[PhoneNo], 'sms',[CellPhoneNo] FROM [dbo].[EmployeeDatabase] Where UserName = ?
He's in the context of a Database user source in manual mode, where you write queries out as strings and they're automatically executed by the gateway internally:
https://docs.inductiveautomation.com/display/DOC81/Database+Authentication#DatabaseAuthentication-ManualMode
You need to return multiple rows, IE a union, rather than additional columns horizontally.
So in theory something like select 'phone', phoneNumber UNION select 'sms', smsNumber... but, we're only going to substitute in a single uname parameter, so you can't just run the same select twice...
From the square brackets/dbo, I'm assuming you're using MSSQL - so maybe you could add an UNPIVOT clause to your existing query to get it into the appropriate format?
Perfect, thank you for clarifying! I didn't realize the information needed to be in multiple rows.