Button to create row and several related rows in another table

[Pablo_DV]

Hi everyone,

I’m trying to create a row, and then create several other rows in another table linked to the original row.

My approach is not very efficient, it should create the task the first time and use it to create the first subtask, and then “overwrite” the task to create the other subtasks. But doesn’t even work for some reason.

What would be the right way to build this?

Thanks in advance!

Pablo

[Pch]

Hi @Pablo_DV !

I’m sorry but what do you mean by this :

@Pablo_DV

and use it to create the first subtask, and then “overwrite” the task to create the other subtasks.

?

I mean, what is your button supposed to do exactly ?

[Pch]

Hi again @Pablo_DV !

As won’t to be able to access my laptop for few hours and as I’m still not sure what you’re actually trying to do, I’m just going to leave a sample doc here to see if this could be what you were looking for …

I’ll get back later to add explanations (if I was going in the right direction) …

[Pablo_DV]

That’s exactly what I was trying (and failing) to achieve. Amazing as always @Pch! Thank you very much!

[Pablo_DV]

Actually, my use-case has an extra complication. The display column of the Task is a formula depending on other fields, so I can’t define the name of the task and store it to create the subtasks.

I’m thinking of using the same formula from the display column to generate the same name within the button formula and using it to create the subtasks, but that doesn’t sound very efficient. How would you do it?

[Pch]

@Pablo_DV

The display column of the Task is a formula depending on other fields, so I can’t define the name of the task and store it to create the subtasks.

Could you share a screenshot of the formula you use in the display field of your Task table ?

So I can take a look as soon as I’m back to my laptop…

[Pablo_DV]

It’s a quite big formula

I have a form that triggers this button upon submission.

The form contains info about 1 or 2 tenants. For each tenant, I create an entry in the People (Personas) database, then create a Contract (Contratos) where both tenants are linked and then I create several documents also linked to the contract (Documentos).

[Pch]

Argh, sorry, I’m here with just a follow-up question once again ( ) but :

Is this what the button in your initial post is supposed to do ?

I mean, if I understand correctly your workflow, you have :

A form to which you add info about tenant(s) in a table and when the form is submitted, an automation pushes a button which adds or modifies rows in various tables (Personas, Contratos, etc …)

Which of these tables is the Task table here ? And which would be the Subtasks table

Unless, it’s a different thing ?

[Pablo_DV]

The task and subtask was just an example of what I was trying to achieve, it has nothing to do with my use-case. Sorry for the confusion!

So de Contract is the higher hierarchical element, it can have several Tenants and several Documents.

So when I receive a form submission, I first create the Tenants in the Personas database, then create a contract with the 2 Tenants (the name of the contract depends on the tenants) and in the last step I create several entries in the Documents database linked to the contract.

[Pch]

Noted !

I’ll get back at you as soon as possible …

[Pch]

Hi @Pablo_DV !

Sorry for the late reply but I encountered some technical issues from my side of the screen … and then, it was simply too late for me to get back here …

So, from the screenshot you shared and the info you added, I came up with a 2 sample docs which seems to somewhat do what you’re looking for (I had improvise a little, especially when it comes to the Documents) .

General Workflow

The workflow should be the same as yours (or at least, very similar)

1. Complete the form with the relevant info concerning the tenant(s) (either both or just the first one)
2. Submit the form
3. Submitted form triggers an automation which pushes a button in the appropriated row in the table where the submissions are stored
   1. Button adds either 1 person or 2 (depending on the form submission) to the table Persons
   2. Button adds 1 contract to the table Contract and links the contract to the person(s) previously created
   3. Button adds 3 documents to the table Documents and links each document to the contract previously created
4. Automation mark the checkbox Disable Button as true to disable the button it just pushed (just in case )

Sample Doc 1

What I understood from your screenshot is that in the form you have a field for each bit of information for the Tenant 1 : First Name, Last Name, Phone Number, Email, etc …

And the same thing goes for the potential Tenant 2 .

When adding the tenant(s) to the Persons table, you add a row per person (so 1 or 2) and their corresponding individual properties (First Name(s), etc…)

The Contract is added to the table Contracts regardless of the amount of tenants submitted through the form. It’s always 1.
And per contract, you create 3 documents (with their own types, etc…).

So, in the sample just below, you’ll see a form ([View of Form Submissions]) displaying only few fields from its source table [Form Submissions].

In the [Form Submissions] you’ll see that I’ve externalise everything the button needs to do what it needs to do .

So, for each submission in the table :

1. [T - Full Name(s)] returns the list of full names of the tenant(s) (which is either a list of 1 or a list of 2 full names)
2. T - First Name(s) lists only the first name(s) of each tenant (either 1 or 2)
3. T - Last Name(s) lists only the last name(s) of each tenant (either 1 or 2)
4. T - Some Thing(s) lists only the “thing(s)” of each tenant (either 1 or 2)

The purpose of those fields is to group within a list the various properties tied each tenant (the 1st
item in the list being a property tied to the Tenant 1 and the 2nd item in the list would be the same property but tied to Tenant 2, if any) so they can be added at once to the Persons table when the button is pushed.

Of course, this means that each property filled out through the form which I didn’t illustrate here (Phone number, Email, etc…) would need their own field where they would be grouped using a formula similar to this one :

ListCombine(
  thisRow.[T1 - First Name],
  thisRow.[T2 - First Name]
).Filter(CurrentValue.IsNotBlank())

This is the formula in the field T - First Name(s) and it simply creates a list with the value in thisRow.[T1 - First Name] (the first name of Tenant 1) and in thisRow.[T2 - First Name] (the first name of Tenant 2) and then filter that list by CurrentValue.IsNotBlank().

So, if the submission has a Tenant 1 and a Tenant 2 it will list both first names. But, if the submission has only a Tenant 1 (which seem to be the mandatory one in your case), it will only return the first name of the Tenant 1.

And as we’re in the lists world, items within a list can easily be counted (which will be useful later) .

The other fields used by the button you’ll see in the table are :

5. T - Contract creates the name of the contract which is added to the Contracts table by the button. The name is mostly created by manipulating lists and depends on the number of full name(s) in the field T - Full Name(s).
   If there’s only one full name in the list returned by T - Full Name(s), it means that only the info for Tenant 1 was completed and it will output something like: 📑 Daisy J..
   If there are 2 full names, it will return something like: 📑 Charles X. & Moira M.
6. T - Docs lists the names of the docs which will be added to the table Documents (depending on the slider on the canvas)
7. Document Types lists the types of each document added to the table Documents through the button, in that order.
   So, when the documents are created :
   • Document 1 (D-1) will be linked to Type 1
   • Document 2 (D-2) will be linked to Type 2
   • Document 3 (D-3) will be linked to Type 3

As for the Button formula, it looks like this :

RunActions(
  Sequence(1,thisRow.[T - Full Name(s)].Count()).ForEach(
    AddRow(
      Persons,
      Persons.Name,
      thisRow.[T - Full Name(s)].Nth(CurrentValue),
      Persons.[First Name],
      thisRow.[T - First Name(s)].Nth(CurrentValue),
      Persons.[Last Name],
      thisRow.[T - Last Name(s)].Nth(CurrentValue),
      Persons.[A Thing],
      thisRow.[T - Some Thing(s)].Nth(CurrentValue)
    )
  ),
  AddRow(
    Contracts,
    Contracts.Name,
    thisRow.[T - Contract],
    Contracts.Persons,
    Persons.Filter(Name.Contains(thisRow.[T - Full Name(s)]))
  ),
  Sequence(1,[Documents Count]).ForEach(
    AddRow(
      Documents,
      Documents.Name,
      thisRow.[T - Docs].Nth(CurrentValue),
      Documents.Contracts,
      Contracts.Filter(Name.Contains(thisRow.[T - Contract])),
      Documents.[Document Types],
      thisRow.[Document Types].Nth(CurrentValue)
    )
  )
)

I’ve tried to comment it hoping it will help, but I think I need to work on my way of commenting formulas …

RunActions(
  /* Action 1: Adds tenant(s) to Persons */
  Sequence(1,thisRow.[T - Full Name(s)].Count()).ForEach(
    /* thisRow.[T - Full Name(s)].Count() counts how many tenants need to be created
     * Sequence(1,thisRow.[T - Full Name(s)].Count()) creates a list of numbers starting at 1 and ending at 
     * either 1 or 2 (depending on thisRow.[T - Full Name(s)].Count()).
     * If there's only 1 tenant -> Sequence(1,thisRow.[T - Full Name(s)].Count()) returns 1
     * If there are 2 tenants -> Sequence(1,thisRow.[T - Full Name(s)].Count()) returns a list of numbers
     * (1, 2) and for each number in the list, add a row ... */
    AddRow(
      Persons,
      Persons.Name,
      thisRow.[T - Full Name(s)].Nth(CurrentValue),
      /* thisRow.[T - Full Name(s)].Nth(CurrentValue) is the list in the field thisRow.[T - Full Name(s)]
       * from which we only keep the Nth(CurrentValue) (and CurrentValue being each number
       * returned by Sequence().
       * For 1 tenant only, 1 item in thisRow.[T - Full Name(s)] and Sequence() outputs 1.
       * So only 1 row is added and the value in Persons.Name will be 
       * thisRow.[T - Full Name(s)].Nth(CurrentValue) -> thisRow.[T - Full Name(s)].Nth(1).
       * For 2 tenants, 2 items in thisRow.[T - Full Name(s)], Sequence() outputs (1, 2) -> 2 rows added. 
       * For the 1st row added thisRow.[T - Full Name(s)].Nth(CurrentValue) -> thisRow.[T - Full Name(s)].Nth(1)
       * For the 2nd row added thisRow.[T - Full Name(s)].Nth(CurrentValue) -> thisRow.[T - Full Name(s)].Nth(2)  
       */
      Persons.[First Name],
      thisRow.[T - First Name(s)].Nth(CurrentValue),
      /* Same as before :) */
      Persons.[Last Name],
      thisRow.[T - Last Name(s)].Nth(CurrentValue),
      Persons.[A Thing],
      thisRow.[T - Some Thing(s)].Nth(CurrentValue)
    )
  ),
  /* Action 2 : Adds the contract to Contracts */
  AddRow(
    Contracts,
    Contracts.Name,
    thisRow.[T - Contract],
    Contracts.Persons,
    Persons.Filter(Name.Contains(thisRow.[T - Full Name(s)]))
    /* Retrieve the person(s) previously created from Persons */ 
  ),
  /* Action 3 : Adds documents to Documents */
  Sequence(1,[Documents Count]).ForEach(
    /* Same as for the Persons table.
     * Sequence(1,[Documents Count]) returns a list of numbers (1, 2, 3) and for each number
     * in the list, add a row ...    
    */
    AddRow(
      Documents,
      Documents.Name,
      thisRow.[T - Docs].Nth(CurrentValue),
      Documents.Contracts,
      Contracts.Filter(Name.Contains(thisRow.[T - Contract])),
      /* Retrieve the contract previously created */
      Documents.[Document Types],
      thisRow.[Document Types].Nth(CurrentValue)
    )
  )
)

Sample Doc 2

Same principle but everything done within the button (with still the help of the automation) .

I’m sorry for the length but I hope this helps !

As I probably forgot things here and there, don’t hesitate if you have any questions or if something is not clear