Hi everyone!
I am working with BigQuery, and I have been able to get the response from a database I have there, the data comes in JSON format, so with a help of an Appian expert, we were able to create a dictionary to make it show from this:
to this:
Now, I need to create records based on this data, just as you can do with the spaceX exercise, where you get data from API, and make a record based on that data without storing it on a table or data base in Appian.
So, I want to do that with this data but when I try to create a record with the dictionary I showed above, I get this error:
I guess is because I am no longer getting the response directly from the API or web service, and I'm manipulating it to create the dictionary, so I was thinking on doing an a!map function but I have not been able to succeed
Any insight onhow I can get a record to display that data?
PS. I need to have it as a record to create a user filter view, to filter some of the fields I'm getting from the response.
Thanks in advance for your time.
Discussion posts and replies are publicly visible
Which functions are you using the get your data from BigQuery? If you're using queryEntity then you should be able to choose RDBMS as the database option
I'm actually getting the data using aconnected system of HTTP type, then I use integrations to get the correct URL to display the data and it returns the data from the bigquery table as shown on the first screenshot above.
From there, I then use a rule to get data to have a dictionary to kind of try to get a key: value thing to show it or have it on a record, however, as you see on the second screenshot, I do get the dictionary created, but then if I try to use it on a record, I get the error.
So, I'm not using functions to get the data and not sure what queryentity does but I'm certainly not using that either.
My guess is you're using some function in your expression rule that is not supported for web service backed record types
Thanks for your response Danny,
I'm using this expression on my rule, this is what is formatting the data
a!forEach( rule!TGSU_bigquery().result.body.rows.f,
createDictionary( {"Duckling", "Duckling_Country","Job_Type","Created_Time", "Last_Activity_Time","Status","Candidate_ID","CandidateID", "Source"}, fv!item.v ))
Am I doing it wrong?
Can you post your entire expression rule? (Please use a Code Box, BTW) - one thing I notice is that the rule should return a DataSubset. In my community environment I'm able to create a new expression-backed record pointing to an expression rule that runs a simple a!queryEntity() call, so I'm unsure what else would be the origin of your error message.
Hey Mike! thanks for your response.
I think you helped me getting that done actually, and the rule is pretty much what's above, as you can see here
This is the dictionary we created with you the other day, to be able to transform the response from f:{v "Argentina"} and so on, not sure if yo remember that
Then My rule is that above, but when I try to use it as data source in a record, I get the error, do you think is because the rule is not returning a datasubset?
I suppose the error may be due to the fact that your rule uses createDictionary(), which is sourced from a plug-in. What happens if you try to set this expression rule as the data source but without trying to enable Data Syncing?
Hello Mike, I tried this and it asks me to select the data source, which I do as the same rule described above , and then data type I select a CDT I created with the name of each field I need. then I get this:
Do you have another way where I can get to define a record type from a response from bigquery?
As that error message indicates, your expression rule would need to be reconfigured to return a DataSubset (or create a new expression rule that takes the result from the original one and wraps it in a dataSubset, i.e. using toDataSubset(), where you pass through the pagingInfo value from a rule input you create.
The result would look something like this:
todatasubset( arrayToPage: rule!TSGU_IndexRows(), pagingConfiguration: ri!pagingInfo )
That did it! So pretty much I will just be missing a few features from syncing, but it gets the job done, thanks a lot Mike, you really have helped me on this man
Great, thanks for confirming
Thanks to you man,
Sorry to bother, I have a follow up question, I am trying to create user filters on this record, but I do not seem to get a field to select from , I got this but not sure where I have to put it in order for me to have it available on the list there