I have four datatypes that are all different types of transactions: Payments, Attendance Fees, Enrollment Fees, and Other Fees. I want to create a list of all of these transactions for a given client using shared schema (TransDate, TransAmount, TransMemo). I would like to emulate a SQL union query to be able to list them in a repeating group and offer sorting and drill down capabilities. I don’t want to waste database space by creating another “table” that would join them all together. Instead I created an API that combines them as an arbitrary text . The issue I am facing is that if one of these tables returns an empty value for one of these “tables”, I get an error, “Non-json value”. I figure it is because it still has the comma I used to separate the data types in the string. How do I get around this?
For clarification, here is my process:
A client is selected and their Client ID, Student(s) ID(s), Start Date, and End Date are passed through to my API.
The dates are cleaned. (If the dates are blank, default dates are selected. If they are not blank, they are checked to make sure they are within specified ranges. If not, they are updated to default start/end dates
Workflow forks are made depending on whether or not Client ID and/or Student ID is blank. (This way the same API can be used to look for data by family or by student, or by both.)
In each fork an arbitrary text is created with this structure: [Do search for Payments:Format as Text, Do search for AttendanceFee:Format as Text, Do search for EnrollmentFees:Format as Text, Do search for OtherFees:Format as Text]
In each “Format as Text”, I have the structure:
{“TransDate”:This Thing’s Date:Extract UNIX,“TransAmount”:This Thing’s Amount:Formatted as 1028.58:Formatted as JSON-safe,“TransMemo”:This Thing’s Memo:Formatted as JSON-safe}
The Return From API is formatted as
Content-Type: Other Content-type
Custom type: Application/json
Everything works perfectly unless a particular student or client doesn’t have a record in one of the “things (tables)” in that given date range. If there is a blank record, it kicks in the error and I get no records returned. Like I said, I think it is because it is adding the comma in step 4.
My question is how do I structure the workflow to check for the empty return before I build my arbitrary text? If I call the data before, I am doing a needless extra search on most of the data. I could query the data first into a variable list and then create a fork action for each case, but (including the forks I have already created, which would each have to be forked for each case) I would have about 32 separate actions in my workflow, most of which were almost identical code. That is not good coding practice. Is there a way to use conditional text statements in the arbitrary text itself to drop the comma if a result is empty?
The easiest solution would be a plugin that allowed combining lists with identical schemes. (My schemas are not identical; I am forcing identical schemas the way you would in a union query.)
If I understand, your arbitrary text do search and have comma between each part, but if one do a search for return nothing, this lead to have ,, without anything between? what if you do arbitrary text:split by,:filtered (not empty):join with , ?
I believe what you described is correct. What you propose sounds promising. I am new to bubble and still trying to wrap my head around everything. How would I structure it with the arbitrary text structure I listed above? I am not sure where/how to place it in the string. I have not used either of those actions before and am not sure of the syntax.
Another concern I have is with performance. Is what I am doing the best practice in regards to performance, or is there a better way to go about this? Is there a good and easy way to just manipulate the json? Simply merging json streams would be far easier and probably more efficient, wouldn’t it?
Little bit hard to answer without knowing input and output data. I was thinking that you “merge” actually all your search into one arbitrarytext and the idea was to use this to split by comma (could be another delimiter if there’s any change to get a comma inside your JSON), remove any empty item from the list, and join them with a comma like it’s should in JSON. This shouldn’t make really a diffeerence in performance from what you are doing actually. Is there a better way? without seeing like I said what you are doing, data used and output needed, it’s hard to answer.
You were correct; that is what I am trying to accomplish, but my questions were:
What is the proper syntax for doing this? Where in the string do each of the elements you listed go? And, yes, if you look at the structure I listed in the first post, you can see that I use commas for separate the key pairs in each of the searches. So would I use something like a pipe to separate each of the searches instead of the commas?
Is the way I am going about this in general–meaning doing it as a merged string–the most efficient and easiest way to do it? Or is there some other type of operation or plugin that would be a better choices? My question was to the process I was using in general, not to your solution to my specific method. I am still trying to wrap my head around Bubble’s workflows and how accomplish SQL equivalent results without adding undo strains on the system. (The “easiest” way would be to nest datatypes and create datatypes to bridge multiple datatypes, but these solutions can be very expensive in terms of complexity of structure, repetition of data stored, and continual calling of data that may not be necessary. A UNION query allows you to merge data that might not be linked together on a regular basis only when you need it while keeping the schema clean and limiting repetition of data that would be required for a join table.)
I got it figured out. I did basically what you proposed, but without the :join With. I used the pipe character as the separator. I need to experiment with it more, but it appears to work.
