Sill new to Bubble. Following another post on the forum I was able to successfully convert a text date to a date in bubble and display this on a screen using an expression and it works great.
I have another situation where I’m using a workflow and reading a SQL record, then in then next step I’m updating a local Bubble database with that data. But, the SQL table has a text date field that I need to update a date field in the bubble DB. I can’t figure out how to incorporate an expression to do this prior to updating the table or while updating the table in a workflow. The expression editor in the “Make Changes to a” (in this case a record in the Bubble DB) forces me to use predefined expressions options and doesn’t allow me to type a text expression using Java. I tired to figure out how to calculate the date in an expression prior workflow step and use the results but I can’t figure out how to even do that. How can I convert a text date to a date in a workflow?
For things like this, I just solve in SQL if at all possible. Then you just map the data type in Bubble and you’re off to the races.
Not sure what SQL DB you are using, but in MySQL, there is a function to convert strings to dates:
SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’)
FROM yourtable
Just change your query in the Bubble SQL Connector to use that function (or a similar one if you’re not on MySQL), map it as a Date data type in Bubble, and you should be good to go.
@joeyg That makes a lot of sense. I’m using Microsoft SQL but I can’t figure out how to convert text to date and change the format at the same time. Bubble doesn’t like the default date format in SQL which is yyyy-mm-dd. I can use Cast(textdate as DATE) and get a yyyy-mm-dd date but Bubble gives an error converting it, it works in SQL. I assume it is because the date format is incorrect. I tried to do a Covert on the date to another format such as mm/dd/yyyy but I can’t get it to work in SQL.
Try creating a test case first by just displaying the date onto a screen and use the “formatted as” option when displaying the date. For example, I use a custom format of ddd mmm d h:MMtt, which displays like Tue Aug 15 4:00pm. I would guess yyyy-mm-dd should work too.
If you can get it to display properly, then you know Bubble has recognized/parsed it correctly. From there, you can figure out how to add it to the Bubble database.
Figured out a solution to this. It requires using the Javascript to Bubble element on the form and updating this element via the Javascript. You also have to create an Event in the workflow and have the trigger event option set in the Javascript to Bubble element to trigger the event to update the database. What wasn’t logical is that the workflow can’t read the value in the Javascript to Bubble element, you have to pass the value to the workflow via an event.
@mishav Thanks for that tip. I read “Asynchronous” in the Javascript step as “Synchronous” so I thought it was running in Synchronous mode. I will test and see if I can read it directly. That should allow me to update the entire record in one step instead of updating the date field in it’s own step.
@mishav I did a quick test, turned off Asynchronous on the Javascript step, turned off the Trigger Event in the Javascript to Bubble element and then attempted to update the date in a workflow step after the Javascript step with the value in the Javascript to Bubble element and that didn’t work. Only way I can get it to work is to Trigger an Event that writes the date in a separate step. Not sure if there is a way around this. I would much rather do one write than two every time I need to modify or create a new record that has a SQL date in it.
@jpayne what does the workflow steps look like? What event starts the JS workflow? what is the JS script? Can you also show where it is being written to the db?
An alternative to putting the write to DB in a “js to bubble event workflow” is putting it in a custom event, and schedule it for example 0.1 secs. This ensures the sequence, and the custom event can be passed parameters.
All, I don’t think you need to use Javascript at all for this. I use dates from MySQL all over my app. It took me awhile to figure out the mapping at first, but now it works just fine without any jiggering. I don’t know why there would be a problem with other SQL db’s.
@jpayne, if you can post your SQL query, Bubble mapping, and db update, maybe I can figure it out.
@joeyg, I think it would work fine if I was using a SQL date field. The MS SQL Database I’m reading was setup with text fields for dates that store a text date formatted as yyyymmdd. I don’t believe Bubble will read that into a date field. It’s the same as if I was reading a yyymmdd date field from JSON I believe. Just text that has to be converted to a field type.
@mishav. This is all being performed in a workflow when the user logs into the application. The workflow steps I tried to use that didn’t work is:
Read User Profile - uses Bubble SQL DB Connector to read MS SQL table for the user profile data including the users text yyyymmdd birthdate field.
Run Javascript DOB - runs the javascript to convert DOB text to Date from step 1 and stores it in bubble_fn_DOB in Javacript to bubble variable on the form.
Make changes to a Profile. - This locates the users Bubble profile, updates all the fields for the users profile from Step 1 including the DOB (date) updated with the Javascript to Bubble element that is storing the bubble_fn_DOB date variable.
What I had to do to get it to work was:
Read user Profile (from SQL DB)
Make changes to a profile (write all SQL DB fields to bubble from step 1 except the DOB field)
Run Javascript DOB (with trigger turned on to run a custom event to locate the same profile record in step 2 and update just the DOB field with the bubble_fn_DOB value.
Ah, I see now. I still think casting the text as date or using some other SQL string to date function would work, but that’s what your javascript is doing so it’s all the same in the end. Glad you figured it out!
