Forum Academy Marketplace Showcase Pricing Features

Pull from a linked Data Type when entering Dynamic Data

I’m struggling to understand how to get data based on relationships I have set up in my database tables. I’m specifically referring to using dynamic expressions to get the data. I’m going to use standard language as I’m still learning to move over to Bubble’s terminology. In your response, please feel free to use Bubble terminology and use old terminology where you think it would add clarity.

How would I type a dynamic expression to get to the following:

  1. The score in the record in the Scores Table circled in red and thus the value of 9.8. (Typically in my SQL string WHERE statement I’d say WHERE ProjectID = 1, Topic = 1)
  2. The Category name of Sport. (Typically in my SQL string WHERE statement I’d say WHERE CategoryID = 1)

Below is an example of a database structure.

Hi,

What is your query starting point, in other words what is the data source (data type) you are coming from?

Also, it could come in handy to screenshot your data types straight from bubble rather than their RDB equivalent… :wink: just to see if you’ve translated things properly in bubble structuring language

  1. The score in the record in the Scores Table circled in red and thus the value of 9.8. (Typically in my SQL string WHERE statement I’d say WHERE ProjectID = 1, Topic = 1)

Do a search for Scores, with the following constraints:

ProjectID = 2
TopicID = 4

… that will return a list of Scores which match those search constraints, so you’ll need to add an operator to specify which item from that list you want to refer to…

… assuming that list only contains a single item (which will be the case when you know there is only one item matching the constraints) it doesn’t really matter whether you use :first item :last item :random item or item #1, although generally :first item is the recommended choice here (it really makes no difference though, as they are all the same item)

Then you just reference the particular field (i.e. ‘Score’)…

So the expression would be: Do a search for Scores [ProjectID = 2, TopicID = 4]: first item's Score

  1. The Category name of Sport. (Typically in my SQL string WHERE statement I’d say WHERE CategoryID = 1)

Again, it’s the same idea… Do a search for Categories [CategoryID = 2]: first item's Category Name

@adamhholmes
Thank you for your response.

The problem is I’m not getting the ID’s as an option after I type “=”. When I manually type them in, they are cleared out as soon as I exit the input box of the dynamic expression.

image

Becomes

image

Assuming the field is of Type ‘Text’ then you should be able to manually type the value… (but you don’t click on the red ‘click’ button to do that… you have to click in the dark grey area to the right of the ‘click’ button…

If it still doesn’t let you manually type a value, then that means the type of the field is not a text…

So you’ll need to look at your database structure and make sure you’re using the correct types.

Thank you. This is a big clue and helpful.

It isn’t text because it is linked to the primary key, series of numbers (I assume). I assume this because when I add the field ProjectID to the Scores table the field type I select is the Project Table. Isn’t that the correct way to link the Scores table to the Project table? Is there something unique I need to do on the “Define primary display fields” screen?

Bubble functions a bit differently than normal database relationships with how you set it up. Are you aware of any good resource that might help me with this? I haven’t found anything searching everywhere (the forum and Google).

Basically I want to link tables and be able to pull in information from a linked table when referencing a table.