I’m looking to see if this is possible within TB or if i would need a 3rd party app.
I need to get the individual values from a connection field and and create a new child record for each.
Example:
We sell a product (mortgage) and attach 1 or more users (customers) to a connection field called Participants.
Then, an action link creates a record in the documents table. this document needs to be signed by all participants. So, i need to be able to get a value of the participants such as record id, in to a list and run an automation creating a new record in the signatures table for each participant in the list.
As you describe two tables, Mortgage Products and Participants with a join between the two with the Connecting field in Mortgages.
Run a scheduled task to generate the automation that then gets all the participant records id’s into your signature record.
If you need a more detailed breakdown let me know. Sounds like your design is correct so is there a stumbling point you’re stuck on?
(You can also use Pipes to create new records too - if you’re not familiar with Pipes checkout out the Tadabase REst API pipe to see the actions you can perform on records. It’s basically the Tadabase API interface for the builder)
@GREDDIE. Appreciate you helping Graham, i don’t think I have explained the data structure properly.
Tables are - Users, Products, Documents & Signatures
Product table - has a connection field called ‘Participants’ to the Users table. Many users to one Product
Each product can have many ‘Documents’
Each Document can have many ‘Signatures’
The only thing i don’t know how to do is to get the users out of the Participants connection field in to the signatures table.
Okay so before we get into the actual problem I’d just like to understand the structure and I have one question.
If the Mortgages are a table of products I would imagine you might want a tables of Mortgage-Applications.
The Mortgage-Application would be unique to that mortgage (property / who is on it etc not the rate of the mortgage for example), if not, could you explain some of the properties of a mortgage record. Is it really the application and your perhaps using a dropdown as a mortgage product ? Do you see what I mean?
Yes, there is a parent table to Products of which a mortgage is a type of product. Other products atatched to the case could be life insurance, hoiuse insurance, unemployment cover, our fees etc. The mortgage product does hold data specific to the mortgage such as rate, loan amount etc.
The parent table is called ‘Cases’ it contains data specific to the case such as Users (customers) property address, estate agents, solicitors along with other child tables for notes, tasks, emails etc.
So, in fact the Users get attached to the Cases table via a connections field but just because there are say, 2 Users attached to the Case doesn’t mean both will be attached to all the products.
an example: 1 User is attached to the mortgage, but 2 might be attached to the life insurance product, or vice versa.
Hey sorry, I’ve reread your original post this morning as I wanted to get back to you quickly as I’ve missed the point your asking as I was too bothered trying to understand the schema and get my head around what you were trying to build (usually that’s a good way to start).
Fundamentally you’ve nailed it, there are some limitations here. For simplicity I find it easier to add this functionality with MAKE. Rightly or wrongly I often break out to MAKE to achieve looping and the creation of records. There are some Tadabase record Pipes and there is a Duplicate Child pipe that I’ve used in the past with some success to create a set of records (like a template) but I found it a little unreliable once the records and connections became more complicated in TB.
Thanks @GREDDIE, i thought as much but wanted to see if it was possible in the app. Time to learn something new i guess. i had a brief look at Make a last year
I’d happily help you with that if you need a hand (foc)
I think the steps would be :
Tadabase:
Create a webhook on the Tadabase table that will hold the record containing the the connection to the multiple participants join. Filter this so you only get events that you want as you may not want all new or updated and perhaps only with a field that has a certain status (participants updated field for example).
Make
Create a scenario that has a webhook at the beginning and place that URL back in the Tadabase webhook you previously created, this will be the trigger.
We then just need to create a loop based on that field’s contents so MAKE creates a “bundle” for each iteration. We would just need to see how MAKE pulls in that data from the loop.
Then inside the loop we can just use the Tadabase Create record module to create each record you need for the signatures.
There will be a few loose ends to tie-up and error handling but it should be a fairly small straight forward scenario.
In the free plan you get 1,000 operations a month so if you estimated that this may take say 15 operations (you may get it down to less than half of this if it’s super simple) then you could run that 67 times a month, say twice day. Triggering directly from the webhook is good as it’s instant but if you’re trying to keep within the MAKE free plan it might be worth looking at running the scenario a couple of times a day at fixed times (if that’s acceptable to your business process).
If you want me to build the scenario out for you to copy, let me know.
So it looks like i am going to need a little help…
I have set up the webhook and Make is receiving the expected data. All good so far.
I am trying to extract the data out of a connection field of users and create a record for each in a seperate table call Participants. Participants only has 3 fields. Name (first, last) email and a connection field to the Case where the original field connection is.
The issue i have is that it seems that Make takes the data as a text field where as in TB its a name field so it won’t put the data in to the new field.
BundleValidationError
Validation failed for 1 parameter(s).
Collection can’t be converted to text for parameter ‘first_name’.
I resolved this first part, i was choosing the Parameter and not the field value when mapping fields in Make.
I was able to run the scenario but the values of the connection field returned back to tadabase were the record ID not the actual value, although the value does show in the results of the webhook in Make.
HI @richardch372 either share a few screen shots of Make or message me and perhaps I can arrange how I can see your MAKE scenario first hand. Sounds like you’re nearly there…
…you could export your scenario to a blueprint that I could import into mine and with you help I could perhaps create a copy of your Tadabase tables that are required…?
However it might simply be be that you’re selecting the Tadabase Join field, what you should be selecting is the value if you drill down to the next level as show below…
If you look at this table I’m joined to the JoinJobHeadertoJobs table but if just select that it will return the RecordID, if you drill-down you will have a Record ID field and a Record Value field.
Alternatively you could make another call to call that Tadabase table directly and pass the recordID to get all values from that table. This will take up another operation in make but if might help you in the short term.
Thanks for this Graham, ill run through everytihng you have put here. I am doing another scenario on Make which is helping me understand it better so that might help with this as well.
i can see in the watch module that the array of the connection field of users is visible twice. the first just shows the record ID but the 2nd breaks the array down in to the collection of the TB name field (title, first, last).
When go to match this data to the fields in the table i want to move the data to, only 1 participants value is available and it will only show the record ID.
I would agree with Tim, it looks like you have two records connected in that join. You can’t therefore place ALL the record values in that connection into a single field, that’s why you’re seeing “{object object}” etc in Tadabase it can’t complete the operation as it makes no sense as such. Do you want to get all the records (Richy Harry, Jayne Harris and any additional records) or do you only ever want the first record (Richy Harry). If you just want the first you should be able to specific that in MAKE by stipulating you want the first record in the array or collection or if you need them all as Tim says you need to step through each one with the iterator
We need to get all records, in this instance thereare 2 but could be less or more.
i was able to set up the iterator yesterday and it successfully creates 2 output bundles from 1 input bundle.
The main issue i am having now is getting that data (which starts out as a name field in TB) mapped to the new table which is also a name field. Error is * Collection can’t be converted to text.
