I am building an app that has to do with the Powerball lottery. Basically, I have created a couple of data types.
One is “Drawing” where I create and store Drawing records that pertain to actual Powerball drawings.
I create a drawing by giving it a “date” and a “jackpot” amount.
The drawing record also has a “winning_numbers” field that set to a text list type and also a “winning_powerball” field that is set to a text type.
I also have a data type for “Tickets” that pertain to the actual lottery tickets that I purchase and enter into the system. Each Ticket has a “serial_number”, a “drawing” that should correspond to a drawing record that I created previously, some “numbers” which is a text list, a “powerball_number” which is a text field and a user or users.
I would like to update the results of each Drawing that I create with the results published in the JSON file. Ideally this would happen automatically but then again I’m not sure since I cannot guarantee the availability of this data from this endpoint so maybe it would be better to just have a form for entering the winning numbers and winning powerball result but if the data is present from the json endpoint that matches the drawing record date, then prepopulate the form with that data. I already have the API setup and I am receiving the JSON just fine.
Then, once I submit the winning numbers and winning powerball results into the drawing record, I would like to find all the tickets that related to that drawing and determine if any of them are winners. I have ZERO clue as to how I would go about doing this. I tried “doing a search” for tickets where drawing = ( in here I want to match the drawing by drawing date) but I could not figure out how to do it.
Once, I am able to find the winning tickets from each drawing, I want to create a notification and send an email letting me know that I have a winner, how many matching picks I have and which ones they were.
You can register for a token, to make the data availability more secure.
If the numbers that you manually enter can be in a different order than the winning sequence, you may want to store them in a sorted order, i.e. “04 05 06” rather than “05 06 04”. This means the search criteria for numbers can look for an exact match.
I’ve managed to get a form that displays the drawings from the json endpoint and seperates the winning numbers into seperate inputs.
For now, the dropdown shows all of the resulting draw_dates. I would like that dropdown to only display the draw_dates for “drawing” records in our data table that have empty “winning_numbers” fields. If they already have data in the winning_numbers field, then we do not need to enter results for that drawing. This is what I am using to get the json results into the drop down…
Then once the “Enter Results” button has been clicked, we need to update the “Drawing” data record with the “winning_numbers” in the inputs.
I am running into problems when trying to do this. Not only can I not filter the json results in the dropdown, but the winning numbers are not being added to any drawing record when I enter results.
I have kind of only scanned your posts so apologies if I have misunderstood or missed something.
For problem 1 (filtering), do I understand correctly that your dropdown is displaying all dates from the API call results (not from your database), but you want it to only show dates (from the API results) for which you don’t already have winning numbers in your database?
If that’s correct then can you show a screenshot of how you are trying to filter this? Can you not have a filter on your Choices source, such that it is something like Powerball Data - Powerball :filtered by constraint date not in (do a search for drawings with winning numbers)'s dates. Sorry, bad pseudo code but hopefully it makes sense.
For the second problem (add to list not working), I have found the options for setting or adding to a list to be problematic. It probably wouldn’t make a difference but is there a reason why you are not using set/add list instead of add, so you just say set/add list ball 1 input’s value :plus item ball 2 input’s value etc?
It seems to be getting hung up on finding matching records because I am using dates as primary keys and then having to format them to get the proper drawing date to display.
For your filter is ‘=’ the only operator available? Do you have ‘In’ or ‘Contains’ as an option? I think the error is because ‘=’ implies a comparison to one thing but your search fro drawings will bring back many things. So ‘Contains’, if available might remove that error.
Not working. The only other option that seems to might work is the “IS IN” option, however, I can’t ever make a match since the dates are always different due to the UTC encoding.
Looking at the results in the evaluator, I am almost getting what I need but the date is off because of the format.
The datetime picker without time option picks midnight at the user’s local time. The date is stored in the database as milliseconds from midnight Jan 1st, 1970 UTC, and then is displayed back to you in your local time.
You can display it in UTC timezone by choosing a text element, then put in a search for the date, then :formatted as custom, Timezone selection Static choice, Timezone ID: UTC.
The powerball data has the date as midnight in UTC time, suggesting that the drawing happened at 4pm your local time. Is this actually what happens? If not, you may want to alter the date from the API before storing it, using +hours.
I suspect that on the data source, the time component is being arbitrarily set to midnight, so the date would be the only meaningful part of the value. This is where you need to get to know your data source more precisely.
Can you work out which timezone the drawings belong to? Then adjust the datetime from the API accordingly?
I think @louisadekoya was on the right track with applying a :filter on the results. Given the gaps between drawings, you could ignore the timezone issue completely and match by both of two conditions: > match_date - 24hrs < match_date + 24hrs
The drawings occur every Wednesday and Saturday evenings at 11:59 p.m. Atlantic Standard Time; 10:59 p.m. Eastern Time; [9:59 Central; 8:59 p.m. Mountain; 7:59 p.m. Pacific] in Tallahassee, Florida.
Looks like the date/time being returned by the API is for Atlantic Standard Time, but has no timezone information in it, so Bubble thinks it means UTC.
To make it accurate, you’d need to subtract 4 hours and 1 minute. Then in daylight savings maybe a different adjustment, depending on what is published (you can check their older data). This would make the date display right for all the timezones you mentioned.
I think I have solved the first part of my riddle. I had to use some trickery to get this to work but I am now able to enter powerball results for drawing records in my database that do not have results. Because of the time issues, what I did was create a secondary date field that instead of being a date format, is a text format instead. Then when I create a drawing, I store the date of the drawing as both a date but also a UTC formatted text version of the date. This makes it easier to match the drawing and the api records. Then I filtered against records that contain the text instead of the date.
