Combining data from several table sources with automation

[Carl_Haugen]

We need some help figuring out how to edit a previously working solution to add a third source of data, and combine it all into a new table. This formula editing goes a bit beyond my capabilities. We currently have 3 source tables and one destination folder. Example shows Table names, Column Names and then data examples in italic):

DB Project
Project Name | Tasks (lookup to Tasks table)
*“New Candidate” | “Contact Candidate”, “Invite candidate”

DB Tasks
Task Name | Project (lookup to project name) | Description
“Contact Candidate” | “New Candidate” | “Description of task”
“Invite Candidate” | “New Candidate” | “Description of task”

DB Candidate
Name
“Thomas Hill”

DB New Tasks
Task Name, Project

The idea is to automate a process to look for new rows in “DB Candidate”. Then to create all the tasks of the project “New Candidate” and to put them in the “DB New Tasks” table with an appropriate Task name to separate all the candidates based on the name of the candidate. Like:

DB New Tasks
Task Name, Project
Task1: “Contact Candidate-Thomas Hill”, “New Candidate”
Task2: “Invite Candidate-Thomas Hill”, “New Candidate”
Task3: “Contact Candidate-Another Name”, “New Candidate”
Task4: “Invite Candidate-Another Name”, “New Candidate”

Previously we did this with a button opening av hyperlink with details of the “New Candidate” project in the DB Project table, writing a new project name in the form in “New Project Title” textbox and hitting the button. The tasks for the project would then be created in the new table with a new project name to diffirenciate between then. The code looks like this (run from within “DB Project”):

RunActions(
thisRow.Tasks.FormulaMap(
[DB New Tasks].AddRow(
[DB New Tasks].Prosjekt,thisRow.[New Project Title],
[DB New Tasks].Description,CurrentValue.Description
)
),
(
[DB Project].ModifyRows(
thisRow.[New Project Title],“”)
)
)

I don’t see a clear way of handling this. Would it be best to add an automation that checks for new rows? I do the new row check with a dummy column with a check mark set to true for all new items, and then check this column for edits. That works as a new row check, even though automation don’t have this function directly.

I could then run a button in the DB Project table as I’m used to, but I don’t understand how I would be able to pick up the “Name” of the added row in the “DB Candidate” table.

Anyone have an ide how to solve this?

[Quentin_Morel]

Hi @Carl_Haugen

Please find a proposal that works !

I set your dashboard as above :

I will indeed use an automation on db candidate creation

For that , I use the CreatedOn value, a “Last Entry” checkbox to see the very last entry. This column will both be used to add the good name on the task, and also to trigger the automation

The steps of automation are there

• I get the “last entry” value
• for each task in your template I add a concatenated name of task, and also the project in the good lookup column

There you go

Please let me know if that is OK for you

Quentin

[Carl_Haugen]

Hi, Quentin. Thanks! This looks like a great solution! My testing so far is good. There’s one snag with the LastEntry formula though…

Our candidate table is in reality a cross-doc synced from individual views for each of our organizations. Synced from our National organization. That way they can just see and act on their “own” candidates. That makes it problematic adding the Last Entry column check on the Candidate source table it self. As this will find the last entry for all organizations’ candidates.

I see a couple of solutions. One could add the column in each of the local Candidate tables, but this would make changes and adding new organizations much more difficult in the long run.

The best way to deal with this (I think) would be to find the last entry for each group of candidates for each of the organizations. We have a column in the candidate table called “Org”. I tried to make a formula like this:

ForEach(thisRow.Org,
Rank(thisRow.Last Entry,thisTable.LastEntry)=1
)

It was a bit naive to think it would be so easy I think This only treats each item of course, not each of the groups of candidates combined. I’m not sure what would solve this.

[Quentin_Morel]

Hi @Carl_Haugen

I assume you then have an “org” column.

Then, just use the Rank function only on value of the table that have the same organization than the current row

It will calculate the last entry for each organization.

BUT !
You need to check also if the content already exist, otherwise it will create the new tasks N times, N=the number of organization (because it will see several “last entry” check). At this point I only check if the NAME of candidate already exist in the task name, but I let improve this to be sure not to have duplicates.

And adapt the if condition of the automation

It seems to works (be patient for the gif)

Please find embed doc, it will be easier for you ! And i’m still available if you need !

Cheers

Quentin

[Carl_Haugen]

Looks like there’s an issue or two here. I recreated the setup in two test pages in separate docs to test with the cross-doc functionality. That broke some things.

Here’s a view of the Database and the view of the DB in the National doc. This is the source of the candidates.

View of the Candidates of Organization A (synced to local organization doc):

Here’s a link to the test page for the “local” organization. By the way, I switched up the DB Tasks and New Task databases. As it made more sense to me according to my main setup.

Not sure if you’re able to check the automation here, but in case not, this is the formulas:

I suspect that the automation with the check for “Row change” does not trigger. And I guess the Already Exist column also would have some problems now, though I do not fully understand what it does. It did not work as expected even when I tested all with local databases (no cross-doc). It never seems to prevent duplicates I got double up of all tasks.

Perhaps we need another solution as some formulas will problably not work as well with Cross-doc?

[Carl_Haugen]

Thanks a lot for the help @Quentin_Morel. You gave me some great ideas I’m sure to use later, even though they did not end up in my final solution.

I had to try to simplify this. There was just to many potential problems here. I came up with something reasonably simple.

A checkbox column (added to the local table and not cross-doc source) that marks “true” when the row is added. This makes it possible to make an automation rule looking for changes to this “Task Not Created” column, and then trigger an automation. This means we have to create this column for all local organizations, but it’s a simple step.

The full automation and tables can be seen here:

[Quentin_Morel]

Sorry I didn’t get time to help you finalizing, but looks like this is good now. Happy to give a good impulsion about how to proceed

Cheers