Display Array According to Order

[Miotx]

Hello All!

I had a quick question about how I store some array data in my database and how I want to display it.

I have an app that basically requests users to enter a bid to get landscaping services done for numerous properties. How it works is that I first select which properties and then send them a bid request. The bidder then enters a price for each service for each store.

When I select which properties, it creates.

e.g. One bidder has 2 properties and they are required to enter their bids for the respective services. Once they submit, then it creates an array into my RetoolDB. There is a column for "Properties" and a separate column for "Bids" in my RetoolDB.

Below is the Property column and an example array:

[
  {
    "property": "1216",
    "region": "Wisconsin",
    "concept": "KK"
  },
  {
    "property": "1294",
    "region": "Texas",
    "concept": "KF"
  }
]

Below is how the BID array looks like. Notice how each key-value is in order so the :

[
  {
    "service 1": "95,10",
    "service 2": "105,1",
    "service 3": "125,1",
    "service 4": "150,1",
    "service 5": "100,10",
    "service 6": "100,1",
    "service 7": "50,1"
  }
]

So If I wanted to query how much are the services for property 1294, I should get this below because 1294 is the second value in the array and I should get back the second value in the Bid Array (hope that makes sense):

[
  {
    "service 1": "10",
    "service 2": "1",
    "service 3": "1",
    "service 4": "1",
    "service 5": "10",
    "service 6": "1",
    "service 7": "1"
  }
]

How can I for example write a query that will find the price of service 5 for property 1294? They are basically in order so the second or third value in the array should correspond to the second or third value for the bids.

Is that possible?

Thanks,

[Jack_T]

Hello @Miotx!

The functionality you described should definitely be possible.

Just had a few clarifying questions. In the bids array (second code snippet) I see that there are two values in red (ex: service 1 has 95 and 10), I was wondering what the first number, the 95, represents. Is that connected to/used to identify the bidder?

My first thought would be to have three values for a service, provider ID number, property number and then the price number (the 10 or 1 value from your examples).

As currently, in the bid array, we know who made the bid and for how much money, but I am not sure how to connect this with which property this bid has been made for.

Unless you have the Bid array set up so that each object containing a list of services is for one single property, matching them first to first, second to second, then we don't have this issue if that is the case let me know and it will help a lot!

You mentioned that you are thinking of trying to sync up properties with bids based on order placement in each array (ex: 1294 is second in property array, and you were thinking that you would thus want "the second value in the Bid Array").

But then you mentioned you want to write a query to get service 5, which is not the second value in the object inside the bids array. Does the bids array have many objects where it looks like this?

[
  {
    "service 1": "10",
    "service 2": "1",
    "service 3": "1",
    "service 4": "1",
    "service 5": "10",
    "service 6": "1",
    "service 7": "1"
  },

  {
    "service 1": "20",
    "service 2": "2",
    "service 3": "2",
    "service 4": "2",
    "service 5": "20",
    "service 6": "2",
    "service 7": "2"
  },

{
    "service 1": "30",
    "service 2": "3",
    "service 3": "3",
    "service 4": "3",
    "service 5": "30",
    "service 6": "3",
    "service 7": "3"
  }
]

If we are certain that we just need to match the index of the property with the index of the services-object in the Bids Array. We can grab all of that data have have access to the right object. then you would be able to key in to the object with the key of the service (ie "service 5") if you know the exact service you are looking for.

You can also use Javascript to loop through all of the services to find the highest bid, etc. I would recommend using the Query builder AI Tool as well to test things out! It is the purple robot in the bottom right corner and is super useful as it can look at you database schema and give you options!