HELP PLEASE! Auto-Fill Form from DB with the ability to modify the form and then submit

Need help Database
1

I am trying to create a form using repeating groups. That part is done. I have the input fields auto-binding to the DB. The staff has to fill the form out daily, and would like the ability to auto-populate the form with yesterday’s data from the DB, and have a Change-All drop down outside the form. The Change-All dropdown form contains the same Option Set of options as the dropdown list inside the repeating group. Ok, I will walk through the workflow. I apologize, but cannot share an editor as it contains some protected info.

When page loads:

Screen Shot 2020-01-17 at 11.55.29 AM
Screen Shot 2020-01-17 at 11.55.29 AM854×189 16.3 KB

The user selects which team they are filling the form out for from the dropdown on the right. The data source of the RG changes on that action, and the list of players load into the RG:

Shot2
Shot2894×843 58.6 KB

If you select the dropdown in the RG, it auto binds that to a DB thing. Same if you select the 2nd Session radio button. The two empty inputs on the right are just me tinkering with different potential solutions.

Problem #1: When the Team is selected from the dropdown, I need the DB value from yesterday’s training session to populate the dropdown in the RG.

Problem #2: If the user clicks the Change All dropdown and selects one of the input options(Option Set from DB) I want that to overwrite all Dropdown inputs inside the RG.

Any help would be greatly appreciated, I have been stuck here for a couple of days. I am wondering if there is a way to create a Custom State for each option in the dropdown list, and then change the state of the group inside the RG to match the State of the Change All dropdown. I am unsure how to pull this off, but I think it could work. Or I am way off and you all will save my sanity! Thanks in advance!

2

As you might imagine, this is going to make it a little more challenging.

First thoughts I would have is: how is your database constructed? Some screenshots of the custom data types (tables) that you have and the fields inside those tables would make it possible to advise you a little better. Could you share some screenshots of the db layout (not the data inside). Also do you have users working out of the development environment? in other words, does your development database also have this ‘sensitive info’ in it? Can you wipe it so you can share and then re-sync with the live environment?

For example: you said you want to populate the results of “yesterdays workout” in a dropdown, but I have no idea where that data is stored in your database or how to retreive it. Is that a value individual value stored for each player inside their db entry or do you store every workout in a separate table and you just want to retrieve a workout that was logged between 12:00AM and 11:59PM yesterday. These are both very different scenarios.

1 Like
3

You make a great point, and I will see if I can make a clean version and share the link. In the meantime. I have a main data type Daily_Player that I POST to from our MS SQL database. We aggregate all data collected in the App with the POST data inside the same data type Daily_Player From here I am open to suggestions. I was thinking of using the internal API’s to write the data collected in the App to both the Daily_Player and Daily_Participation tables. I believe this would allow me to quickly report from the smaller Daily_Participation table. Here are the two Data Types I am using:
Screen Shot 2020-01-17 at 2.32.50 PM

Daily_Player has 66 Fields and Daily_Participation has 10:
Screen Shot 2020-01-17 at 2.41.34 PM

So all data is stored in Daily_Player, and I am planning on leaving it in the Bubble DB for 30-60 days depending on size and speed.

I am intrigued by your idea of write to a new table daily. Thank you for your response and I appreciate any additional help available!

4

I’ll just assume that your app’s integration ‘is what it is’ and that its working for you.

Does all of your data wind up in Daily_Player?

Right off the bat it looks like every single one of your data types in Daily_Participation is the custom type Daily_Player. This is roughly equivalent to a ‘foreign key’ to use SQL terminology. Unless you are doing something something very strange with your database architecture, I doubt this is what you want.

In your picture:

  • The field (column) Date points to another thing (row) in the Daily_Player type (table)
  • First Name points to another Daily_Player row.
  • Last name points to yet another Daily_Player row.

It’s not clear what daily_participation is trying to do here. It literally holds no data of its own, which isn’t inherently wrong, but based on what you’ve got here and your questions, I’m almost certain that’s not what you want to do (or if you need Daily_Participation at all).

Can you share some screenshots of Daily_Player

5

To add onto my previous comment. Does Daily_Player have a new entry for every player, every day? For example: If ‘Michael Jordan’ played three times this past week do you wind up with 3 entries in Daily_Player

Example:

(DB Entry 1)
First Name Micheal
Last Name Jordan
Team Bulls
Practice Date 1/16/2019

(DB Entry 2)
First Name Micheal
Last Name Jordan
Team Bulls
Practice Date 1/17/2019

(DB Entry 3)
First Name Micheal
Last Name Jordan
Team Bulls
Practice Date 1/19/2019

If so is there any sort of unique ID that is assigned to each player?
Example:
First Name Micheal
Last Name Jordan
Team Bulls
Player ID 13523

First Name Steph
Last Name Curry
Team Lakers
Player ID 23453

6

Thank you for your response, and you answered one of my follow-up questions about field type. To answer your MJ question, yes, there is a single daily entry for each player upon submission of this form. Here is a snapshot of the Daily_Player DB, it is set up with the expected values, except the duplicate DailyParticipation which I was tinkering with. I didn’t know if those alternate Data Types being available would somehow sync a “Like” field across multiple tables. I am obviously new to DB architecture, but upon your explanation absolutely understand the error of my ways! I really appreciate you taking the time to look at this!

Screen Shot 2020-01-17 at 5.09.51 PM

7

You mentioned a SQL database. I didn’t want to muddy the waters earlier by getting into this, but I feel like now is an appropriate time to discuss since it may affect how you’ll want to proceed.

  • Does the data from your app originate outside this app (like say a website with an SQL database) and is then imported via an API?.. Sounds like yes or was this a one-time import?
  • if so, is there a regular import schedule (realtime? daily?)
  • if so, do you (modify that data, add new data, etc) after you receive it in bubble?
  • if so, does that data need to make its way back to the SQL database
8

Ok, I have recreated the issue on a different app. Here is the editor

To answer your questions above:

    • Does the data from your app originate outside this app (like say a website with an SQL database) and is then imported via an API?.. Sounds like yes or was this a one-time import?
  • Yes it originates from outside the App and we POST to the Bubble DB daily.
  • if so, is there a regular import schedule (realtime? daily?)
  • Yes, we have a watcher on the 3rd party API side and one on our DB.
  • if so, do you (modify that data, add new data, etc) after you receive it in bubble?
  • We modify before we push to Bubble
  • if so, does that data need to make its way back to the SQL databaseList item
  • We are using Bubble as a temporary DB with a rolling 30-60 days stored locally

I hope that helps and I think the editor will help even more. Thanks in advance!

9

Sorry about the formatting, I am not sure what happened.

  • Does the data from your app originate outside this app (like say a website with an SQL database) and is then imported via an API?.. Sounds like yes or was this a one-time import?

  •        Yes it originates from outside the App and we POST to the Bubble DB daily.

  • if so, is there a regular import schedule (realtime? daily?)

  •          Yes, we have a watcher on the 3rd party API side and one on our DB.

  • if so, do you (modify that data, add new data, etc) after you receive it in bubble?

  •           We modify before we push to Bubble

  • if so, does that data need to make its way back to the SQL databaseList item

  •           We are using Bubble as a temporary DB with a rolling 30-60 days stored locally
10

I’m sorry if I’m being pedantic, but getting clarity will help me understand a little better just to make sure I’m not missing anything. Can you provide me a yes/no answer (with further explanation if you feel necessary) to these questions.

if so, do you (modify that data, add new data, etc) after you receive it in bubble?

      We modify before we push to Bubble

Is your answer a ‘no’ then? You do not have a need to modify or update the data in your bubble db? If its is a ‘no’ then You just want a way to display the data you have? A ‘yes’ is fine too :slight_smile:.


if so, does that data need to make its way back to the [external] SQL database

We are using Bubble as a temporary DB with a rolling 30-60 days stored locally

Is your answer a ‘no’ then? The data stays for 30-60 days and then expires (presumably deleted?) You don’t need to send your bubble data anywhere (at least for now?)

Can you get a unique player ID value from your API and save it into Daily_Player?
(See my previous example of a unique ID above) . The reasoning for this is that you might have two players with the same name, a given player on multiple teams, etc. A unique ID would make it very easy to look up a specific given player’s entries in Daily_Player The only unique thing you have now to identify your players in your bubble DB is are DisplayName, which is less than ideal. If for example, let’s say that ‘Michael Jordan’ goes to the league and changes his name to ‘Mike Jordan’ because he doesn’t like the name ‘Michael’ showing on his stat sheets… I dunno maybe his mom entered the wrong name on a form. Presumably your external API starts exporting ‘Mike Jordan’ to the bubble db once that change has been made. It will then be impossible to match up records for ‘Michael Jordan’ and ‘Mike Jordan’. Your current setup doesn’t have enough information to be able to handle that.

HOWEVER, based on what you do have now.

Here you go…
Check out a new page I created in your test app called /index2

To prefill your dropdown with the ‘most recent recorded’ value for this player (this is different than ‘yesterday’ value which, will return an empty value if there was no practice for that player yesterday.

Set the placeholder for your dropdown using a search for Daily_Player
https://cl.ly/b5c6060c5e9d/Image%202020-01-18%20at%204.10.50%20PM.png

Inside your search for expression set the constraints to your player name and their team. This should return only this player’s information in Daily_Player, but a unique ID would be better to ensure they are the same.
https://cl.ly/b41da1f27f45/Image%202020-01-18%20at%204.09.28%20PM.png

This will return a list of Daily_Players matching a player with this name and this team sorted by the date in trainingDate starting with the most recent first. You now want to take the first value and put it into your dropdown placeholder using :first item

https://cl.ly/f43e52d834c4/Image%202020-01-18%20at%204.12.22%20PM.png

That should get you where you need to go. There’s likely a few other things to sort out as well, I’m sure.

11

Thank you! To answer your other questions:

if so, do you (modify that data, add new data, etc) after you receive it in bubble? Yes

if so, does that data need to make its way back to the [external] SQL database Yes

12

Hey there,
I’ve updated my post above. Please take a look :slight_smile:

13

If you are going to send this data back to your SQL database, you really should get an ID exposed to your API so when you push the data back, you can validate that it stays in parity. Just my 2 cents