How to deduplicate form lists

[Carl_Haugen]

I need a way to only display unique values in a form, but I can’t seem to use the filter function or unique() formula right. The users applying to the form can not see two duplicate values.

Here’s the form list that includes two “Oslo” values for cities.

We have an issue where we have two organizations in one city (Oslo). And we need one row in the Form result table for each of the organizations in each city. The admins in each organization need to be able to update status on their own row.
We have to have both values because the Org column is also used to keep track of wich organizations we have in each city.

The list is looked up from this table (city and organization columns):

I tried several formulas like filters and unique for the City list (first image). I used lookup options, Option settings, Custom Filter and formulas like “Unique(thisTable.By)”, but that did nothing.

Anyone have some smart ideas as how to approach this?

[Quentin_Morel]

Hi @Carl_Haugen

Would you mind sharing a link to your document, either here or in private message if necessary, so that I may have a look on this to understand how to solve your problem

Thanks

Quentin

[Carl_Haugen]

Hi @Quentin_Morel

I created a duplicate doc with the matching tables and form. It’ll show what I want in a simpler manner I think. It’s an open editable doc for all, in case more want to check it out.

[Rickard_Abraham]

I gave it a try

• Change column type of City to a Select List
• Give it the options: [DB Org Contact].City.Unique() - The trick is to use Unique on the Name column, not the whole table
• Create a new column DB Org Contacts with formula [DB Org Contact].Filter(City=thisRow.City) - Since City is now just a Name we need to query to get the correct city. Not sure what your desired output is so I just made it return all matching cities

[Pch]

Hi @Carl_Haugen

So, I did something different (See the page OrgInfo 2 in the sample doc you shared) when it comes to linking you datas …

Having 2 “Oslo” and each of them tied to a specific organisation doesn’t make much sense in terms of data structure.

There’s only one Oslo in Norway but in Oslo, there could be many organisations .
And, each organisation within a city has its own address (the city should be a property of this specific address).

You couldn’t deduplicate the Oslo here within the Filter in the Options settings of your lookup because those 2 “Oslo” are in fact different :

• The 1st Oslo has the property Org Oslo attached to it
• The 2nd Oslo has the property Org Oslo 2 attached to it

So despite the fact that both cities have the same name, they are in fact 2 unique and distinct rows/values in your table [DB Org Contact] .

In the page OrgInfo 2, you’ll see that I’ve added a lookup field to the table [DB Organization 2] from the table [DB Org Contact 2] to select a city where each organisation is located (as said before each organisation should only be located in a city but a city can contain many organisations… This link reflects that )

And then in the table [Form - Result], I first added a lookup from the table [DB Org Contact 2] where one can select a city and then a lookup from the table [DB Organization 2] where one can select the appropriate organisation.

The lookup [DB Organization 2] has a filter (in the Option settings of the lookup) set to only suggest organisations located in the selected city (in the lookup field [DB Org Contact 2]) as that connection was made earlier by linking a specific city to a specific organisation.

This all means that when one uses the form, they should first select a city then a specific organisation and enter some datas .
But it also means that each row added through the form will also be unique (one organisation from one city)

I’m not sure if this fits you use case though

[Christiaan_Huizer]

wonderful contribution @Pch
it is indeed all about the correct data architecture.

[Carl_Haugen]

Thanks @Rickard_Abraham. I tried to follow your guide, but I ended up with a a single organization for the cities where there is just one, but duplicates for Oslo. Not quite what I was expecting. I might have messed this up though. And it looks like the Form City field is just a Select list of the unique cities, added manually. I’d like to have this automatically pick up from cities in the table, so we don’t have to change in several places as organizations are added or removed. I try to minimize overhead for the admins taking over this project

@Pch You are absolutely right that it would make more sence data structure wise to have two organizations connected to one City.

Your idea would in fact give the user browsing the form, just one single entry to select if he/she lives in Oslo. The problem is that they do not know at all if organization 1 or 2 is a better fit for them. So each of the two organization have to contact them to have a talk. That’s why I would like it to be only one choise on the form, but two rows in the form result table. We cross-doc those candidates to each organizations task management system.

What would be the best way to build on this, to add two nearly identical roles (except for “Org Oslo 1” and “Org Oslo 2” as Organizations? As it stands now, the Result table will end up with one row, with each of the two organizations if you select Oslo. This is a problem as we have to separete the rows in two similar unique rows, because the organizations want to have their own status and notes for each candidate.

We can’t edit this directly as it’s lookup data. I can’t just add an automation to remove one of the organizations, and duplicate the row and change it to the second organization. As my previous idea was.

I cleaned up the naming of tables and colums to make more sence, and removed unneccessary form items. Should be a bit easier to grasp for all now.

[Pch]

Hi @Carl_Haugen !

@Carl_Haugen

That’s why I would like it to be only one choice on the form, but two rows in the form result table.

This is not directly doable with forms, as forms follow a “1 submission = 1 row in the corresponding table” rule.
A form can be submitted multiple times but that wouldn’t help you here.

I still don’t know if this would fit your use case but I tried 2 things :

You’ll find the first one on the page called OrgInfo 3 where I’ve added buttons to the table [Candidate Form - Result 2] and another table called [Final Form Result].

The table [Final Form Result] is meant to be the table storing the “deduplicated” submissions (if the deduplication is needed) and the deduplication is done with either one of the 2 buttons in [Candidate Form - Result 2].

The first one (the blue one, Add to Final form Result - 1) does 2 things :

1. Depending on the number of organisations in thisRow.Organization it will add as many rows as needed to [Final Form Result] and copy the relevant info stored within thisRow.

2. Check the checkbox next to it ([Was added to Final Form Result]) so the button can be disabled (to avoid accidental click and duplicates values in [Final Form Result])

   

The second button (the purple one, Add to Final form Result - 2) does something similar: it deduplicates the row if needed and copy the relevant info in [Final Form Result] but instead of checking a checkbox, it deletes the row where it’s sitting (to avoid unnecessary clutter of the table [Candidate Form - Result 2]).
So, with this button the rows are literally moved from a table to another .

Generally speaking, in this case, the table [Candidate Form - Result 2] becomes a temporary/intermediate table, only there to store submissions (could serve as an archive of the submissions, a log) and the table [Final Form Result] is the table that would contain all the info the organisations needs to contact the candidates (in other words, this table should be the one to cross-doc) .

Now, the problem with this is that if you can’t set up an automation to push one button or the other once a day (at night), for example or similar, this will not help you either .

---

The other possibility I saw, was to get rid of the form and replace it by a one row helper table displayed with a Detail layout to simulate a form, for 2 reasons :

1. As I said earlier, when using a form 1 submission = 1 row
   But, at the submission, more than one row should be submitted here
2. The easiest way to deduplicate what’s needed I can think of is using a button…
   But buttons don’t work in forms and the initial Submit button of a form can’t be hidden or avoided. So trying to go around the issue with a button can’t be done within a form.

You’ll find this possibility on the page OrgInfo 4 .

I used a view of the helper table called Candidate submissions so you could see what it looks like and how it works as a table and as a simulated form at the same time.

The Candidate submissions helper table is practically the same as your [Candidate Form - Result] :

• 1 text field for the candidate’s name
• 1 lookup field to select a city
• 1 lookup field with the corresponding organisations in the selected city

… and, a Submit button.

The Submit button works similarly to the ones in my previous suggestion :

1. Depending on the number of organisations in thisRow.Organization (in [Candidate submissions]) it will add as many rows as needed to the table [Candidate Form - Result 3] and copy the relevant info stored within thisRow.
2. Modifies thisRow and reset the editable values to Blank ("")

Now, a problem I can see here is that you can’t validate a field (well, that you still can do, by adding more fields to the table and checking the values in the fields of the “form”) or mark it as required like you can easily do with a form… So, for example, a candidate could submit a half-empty form.

To avoid this, there’s a Disable If formula which keeps the button disabled as long as all the fields that would be required (the candidate’s name, a city) are not filled with something .

In this case, the table Candidate Form - Result 3, where the row(s) are sent once the Submit button has been clicked is the table that would contain all the info the organisations needs to contact a candidate

I can’t say if any of the 2 options would help you as they both have their specific pros and cons but that’s what came to my mind

[Carl_Haugen]

Hei @Pch. Thanks a lot for the excellent suggestions, and the effort you put into helping me out. That’s very much appreciated!

I’ve tested all of them and the Orginfo 3 suggestion is probably closer to what we need. However, I wanted to try to achieve this with the original result table. As to not complicate and confuse the poor guys going to take over this after my initial setup

I took your suggested formula to the button column named “Split Test” button and added a checkbox column named “Not Split”.

My thought is as follows
The checkbox serves two purposes.

1. It trigger an automation for all new rows. The automatic setting of the checkmark on all new rows means that the automation should run on everything added (testing against changes on this row).
2. It also serves as a way of telling apart the candidates that are duplicated.

The “Split Test” button checks for items in the Organization column. If only one item exists, it set the “Contact Org” the same as the “Organization” column. We use this Contact Org column as a way to filter views for cross doc sync of appropriate candidates to our local organizations

When Organization column contains more than one organization, n number of rows are added for each organiation, and the “Not Split” checkmark is removed. I hope this last part will not trigger another automation run for the changing of the “Not Split” column value. It seems not to. After the rows have been added, the original row is deleted.

I hope this will not give me some nasty edge case problems. Seems to be working ok so far.

[Pch]

Hi @Carl_Haugen !

I’m very glad to know and see that my suggestions helped you with this !

@Carl_Haugen

I hope this last part will not trigger another automation run for the changing of the “Not Split” column value. It seems not to. After the rows have been added, the original row is deleted.

For this, I slightly modified your Split Test button to disable it when its job is done (i.e.: when it has been pushed by the automation) using the value in the checkbox [Not Split](there could be other way to disable it though) .

So when the automation runs, only the enabled button in the table will be available to the automation and this should only happen when there’s a new row added to the table .
After that, even if it’s triggered again by the modification of the checkbox and the button(s) has already been pushed, there should be nothing left to push again .

Like for my other replies, this is just an idea