Using SQL results as choices

#1

Hi there

We are using FlowXO to perform functions that our staff would otherwise have to perform manually.

In my flow called Visit us for an Open Day, I am asking a user to choose from a Campus to visit us. At this point we used to manually maintain the date options for the visits in FlowXO but we want to now use a MySQL query to present such things. I have made a MySQL query to query the table that contains the list of Dates and Times that are relevant for the campus choice. The result fetches more than one row.

I now need to be able to present those Dates and Times as Options (with the values as the Event ID of the corresponding row passed back as an answer). I’m struggling with how to deal with this element of the flow and wonder if you can help.

I have pasted the relevant parts of the flow, query and query result below.

Thanks for any help you can give

Oli

PastedGraphic-1.png1934×1122 187 KB
PastedGraphic-3.png1628×1082 89.7 KB

Result
{
“rows”: [
{
“EventID”: “TEST123”,
“Campus”: “ACM Guildford”,
“Date”: “2017-10-21”,
“Time”: “11:00”,
“Type”: “Open Day”
},
{
“EventID”: “TEST456”,
“Campus”: “ACM Guildford”,
“Date”: “2017-10-29”,
“Time”: “11:00”,
“Type”: “Campus Tour”
}
],
“fields”: [
{
“catalog”: “def”,
“db”: “bot_db”,
“table”: “catalogue_openday”,
“orgTable”: “catalogue_openday”,
“name”: “EventID”,
“orgName”: “EventID”,
“charsetNr”: 33,
“length”: 36,
“type”: 253,
“flags”: 20483,
“decimals”: 0,
“zeroFill”: false,
“protocol41”: true
},
{
“catalog”: “def”,
“db”: “bot_db”,
“table”: “catalogue_openday”,
“orgTable”: “catalogue_openday”,
“name”: “Campus”,
“orgName”: “Campus”,
“charsetNr”: 33,
“length”: 300,
“type”: 253,
“flags”: 0,
“decimals”: 0,
“zeroFill”: false,
“protocol41”: true
},
{
“catalog”: “def”,
“db”: “bot_db”,
“table”: “catalogue_openday”,
“orgTable”: “catalogue_openday”,
“name”: “Date”,
“orgName”: “Date”,
“charsetNr”: 33,
“length”: 30,
“type”: 253,
“flags”: 0,
“decimals”: 0,
“zeroFill”: false,
“protocol41”: true
},
{
“catalog”: “def”,
“db”: “bot_db”,
“table”: “catalogue_openday”,
“orgTable”: “catalogue_openday”,
“name”: “Time”,
“orgName”: “Time”,
“charsetNr”: 33,
“length”: 15,
“type”: 253,
“flags”: 0,
“decimals”: 0,
“zeroFill”: false,
“protocol41”: true
},
{
“catalog”: “def”,
“db”: “bot_db”,
“table”: “catalogue_openday”,
“orgTable”: “catalogue_openday”,
“name”: “Type”,
“orgName”: “Type”,
“charsetNr”: 33,
“length”: 300,
“type”: 253,
“flags”: 0,
“decimals”: 0,
“zeroFill”: false,
“protocol41”: true
}
]
}

(Daniel Beckett) #2

@osacm

I see that you’ve sent this into the support inbox. I’ve responded over there :slight_smile:

(Fabio) #3

I also have a similar question, would have been nice to have seen the fix

(Daniel Beckett) #4

@fabzster

Here’s the response I sent to Oli at the time:

"Our docs for Data Outputs detail how to select data from returned query results. To make the data easier to work with you could copy your data into a Json Parser to get a formatted list

Am I right in thinking that the important data that you want to show is contained in:

{
"rows":[
{
"EventID":"TEST123",
"Campus":"ACM Guildford",
"Date":"2017-10-21",
"Time":"11:00",
"Type":"Open Day"
},
{
"EventID":"TEST456",
"Campus":"ACM Guildford",
"Date":"2017-10-29",
"Time":"11:00",
"Type":"Campus Tour"
}

The data is nested inside of ‘rows’ in an array which means that the outputs start from 0 and increment by 1. There’s two sets of outputs for rows so we have rows__0 and rows__1. Nested inside each of these are five fields for ‘EventID’, ‘Campus’, ‘Date’, ‘Time’ and ‘Type’. To use the output you insert the field name, e.g:

rows__0__Date
rows__0__Time

Which should return:
2017-10-21
11:00

To use the second set of data it would be:
rows__1__Date
rows__1__Time

Which should return:
2017-10-29
11:00

When setting up a question you can have the text displayed for the choice and the value saved when that choice is selected. If you were to put the date & time as the choice you could then specify EventID as the value using the above method.

Although it’s not an exact match to what you’re doing the data outputs section in our tutorial for Using Data from the Web in Flow XO shows further examples of inserting data into your flow actions from a set of data outputs.

Hope that helps! :slightly_smiling_face:

Thanks,
Dan"

1 Like
(Fabio) #5

Exactly what I was looking for thank you so much!!!