Importing a JSON array via API connector

Need help APIs
1

Hi All,

I have the following table in an external backend that I have made available in the backend API as a JSON object:

image
image1492×216 10.3 KB

I’m essentially just trying to recreate this table in my bubble frontend via a repeating group.

When I get the JSON file via the API connector, it is making each row in the table available as one result, instead of each column being a separate field, as you can see here:

image
image816×496 26.2 KB

I’m not a coder and a little out of my depth so if someone could point me in the right direction that would be great!

I have control over the backend and may be able to export each column as a separate JSON object if necessary (would this make it work?) but I want to see if I’m missing something more obvious first.

Thanks for your help.

2

@mishav @JoshP @pking06 @NigelG sorry to ping you but I noticed you have explored similar questions in the past. Hoping you may be able to assist with this one.

3

@jarrad @nicolas_dap you might be able to help with this one?

Would changing my request to a post and using the post body work?

4

You could try to turn the list into array using the various flatten/stringify methods.

5

Hi @NigelG, sorry could you elaborate a little bit, I’m a bit of a novice and don’t fully understand how it all works.

In my backend I am creating an array and then using JSON.stringify to convert the array into a JSON object. I have copied the relevant code below.

Are you suggesting that Bubble will only import this as a list, so I should use a tool of some kind to convert this list into an array within bubble, and then once I have the array, I can create separate lists for each column, which can then be used throughout my page as I please?

If this is right could you give me just some high level guidance as to how I would go about this?

var resultArray = [];

		for (var i = 0; i < results.length; i++) {

		    var value1 = results[i].getValue(projectSearch.columns[0]); 
		    var value2 = results[i].getValue(projectSearch.columns[1]); 
			var value3 = results[i].getValue(projectSearch.columns[2]); 
			var value4 = results[i].getValue(projectSearch.columns[3]); 
			var value5 = results[i].getValue(projectSearch.columns[4]); 
			var value6 = results[i].getValue(projectSearch.columns[5]); 
		
		    
		    resultArray.push(value1 + ',' + value2 + ',' + value3 + ',' + value4 + ',' + value5 + ',' + value6);
		    
		}
		
		return JSON.stringify(resultArray);
6

Can’t help with the code I am afraid.

But I think what you need to do is get it into a form that looks like …

“names”:[ “Amy”, “Carla”, “Ben” ]

So bubble knows what to call the list.

7

@NigelG thanks to your help, I eventually (after more hours than I care to admit) figured this out. I now have the data returning as:

{“project name”:[“A Plus Stationers : Tower Installation-AU1”,“A Plus Stationers : Tower Installation Network-AU2”,“A Plus Stationers : Communication Installation”,“A Plus Stationers : Fiber Cable Installation Project-AU4”,“A Plus Stationers : Test 5 Project”,“A Plus Stationers : Test Installation”],“project status”:[“In Progress”,“In Progress”,“In Progress”,“In Progress”,“In Progress”,“In Progress”],“project startdate”:[“2/8/2017”,“17/8/2017”,“16/8/2017”,“5/8/2017”,“1/12/2017”,“7/8/2017”]}

Which basically lets me select each column as a list, which is exactly what I was after.

What I’m trying to now is to display this data in a repeating group in bubble, with a strong preference for never actually storing the data in the bubble database.

From the reading I’ve done on the forum, the best way to do this is to create custom states in the index of the page with the data that I want to use throughout the page, which I’ve done. So I now have a list of “project names”, a list of “project status”, and a list of “start dates” all as custom states in my index page which are working as expected.

The problem I’m having is that it seems to be that the child elements within a repeating group have to be fields of the type associated with the repeating group. So I’ve created a type, “project”, with the fields I’m after, being project name (list), project status (list), and project start date (list), but I can’t “push” the data saved in my index into my repeating group’s project’s fields.

I think as a workaround I could just create separate repeating groups next to each other, one for each column, but there must be a way to do it properly?

Are you able to help with this? Thanks in advance…

8

Using the data structure in your first post …

[ "PRJ1077,A plus ...,2,blah,bloh", "PRJ1078,A ..." ]

This is a list of comma-delimited text. This list can be sent to a custom state (list of text) or a repeating group (data type text).

A repeating group’s data can be set in its properties (so it picks up from a source dynamically), or by a workflow step (so the workflow determines when the data is updated).

Each item you could extract into separate text fields with :extract with Regex. This could be done in the RG cell, for example.

The disadvantage of this is having every value in a text format, making it a little harder to work with.

For your next data structure …

{“project name”:[“AU1”,“AU2”],“project status”:[“In Progress”,“In Progress”],“project startdate”:[“2/8/2017”,“17/8/2017”]}

You end up with a list of project names, a list of project statuses, a list of start dates. The lists are related to each other by position.
You could assign each list to a different custom state.
Then have, for example, a RG based on the list of project names, and in each cell, show the nth item in the list of start dates; n being the current cell index.

The disadvantage to this, is your lists are vulnerable to being altered severely by Bubble’s “duplicate value removal”, that happens whenever you do a Bubble list function on it, like add item.

I think Nigel was pointing you to having a structure like this, so that Bubble would behave nicely with it:

[ { "project name": "AU1", "project state": "going somewhere", "start date": date-format-that-bubble-likes },
{ "project name": "AU2", "project state": "going elsewhere", "start date": date-format-that-bubble-likes } ]

List of project objects, each object having properties: name, state, start date.

Then Bubble would have a list of objects, each object having a property you can use like item’s project name.

9

@mishav thank you so much! I just spent a couple of hours working with my backend to configure the data like this (my javascript is at the level of a struggling toddler), but now it is working exactly as I wanted it to.

This was a major requirement to get my idea off the ground so I’m really happy to get it working. Thanks also to you @NigelG

1 Like