Insert only unique rows ignore existing

[Anonymous]

Hello everyone,

Before writing this question, I have gone through many similar articles but didn't find any related to my problem.

Here i only want to insert unique rows, and ignore existing.

So, First I am capturing all my records from ProjectKPIDefinitionTable and storing it into a collection PKPI. And then I am renaming column so that it doesn't create confusion because the collection we gonna compare is having same column name and i have given name RenamedProjectKPI1.

And then I am looping through all my rows in ProjectKPIDefinitionTable and comparing with RenamedProjectKPI1 and taking the count. So if count is greater than 1 and where project_id and kpi_id matches then it should ignore that row and check for another. If there any row which is unique so here it should patch. As you can see it on attached screenshot.

So I have wrote the code and it throwing expect rows error which is true.

I need help here so that this code should work!

Thank you.

[RandyHayes]

@Anonymous 

Your formula has several errors in it.  You also do not need to make so many copies of the same data into internal collection tables for this to work.

You appear to have another collection in play in your formula, but I cannot see it as the error tooltip is covering it.

Can you provide the full formula to see all of it?

[Anonymous]

This is the code I have used.

// Collecting existing data
ClearCollect(PKPI, '[dbo].[ProjectKPIDefinitionTable]');

//Change multiple column names code
//On this collection "ProjectKPI1" I am storing data from fields
ClearCollect(
    RenamedProjectKPI1,
    RenameColumns(ProjectKPI1,"project_id","PID","kpi_id","KPI","kpi_datatype","KDT", "pnl_type","PT", "kpi_bucket", "KB", "kpi", "KN","kpi_definition","KD"));

//Rest checking if already exist or not then Patch
ForAll(RenamedProjectKPI1,If(CountRows(Filter(PKPI,kpi_id=KPI && project_id=PID)) > 1,"Exist", Patch('[dbo].[ProjectKPIDefinitionTable]',{project_id:PID, kpi_id:KPI, kpi_bucket:KB,kpi:KN,kpi_definition:KD, pnl_type:PT, kpi_datatype:KDT,RowCreationDate:Today()})));

[RandyHayes]

@Anonymous 

What I need a slight clarification on...in your Formula you are checking if the row count is *greater* than 1.  Is this to say that you would have duplicate items that match the kpi_id and project_id ?  OR, is it that you are trying to have only 1 record in your table and it is that record that you wish to update?

I'm not entirely clear on your logic in that part of the formula.

[Anonymous]

Thanks for asking.
No I don't want to update. Either insert or ignore (I think I have missed the Defaults Code inside the Patch) :(, So logic is very simple. It is if the count > 1 then I dont want to insert because record already there so ignore, but if count is 0 that means this record in not there in my SQL DB table so I want to insert it.

[RandyHayes · Accepted solution]

@Anonymous 

That is why I asked - because your logic on that is a little off.  If you look for >1 then you are only going to trigger on 2 records or more.  You really want to look at >0, meaning 1 or more.

So, your formula should be the following:

With({_uniqueList: 
    Filter(ProjectKPI1 As _item,
        !(LookUp('[dbo].[ProjectKPIDefinitionsTable]', 
                kpi_id = _item.kpi_id && project_id = _item.project_id,
                true
          )
    )
},
    Collect('[dbo].[ProjectKPIDefinitionsTable]',
        ForAll(_uniqueList As _item,
            {kpi_id: _item.kpi_id,
             project_id: _item.project_id, 
             kpi_id: _item.kpi_id, 
             kpi_bucket: _item.kpi_bucket,
             kpi: _item.kpi,
             kpi_definition: _item.kpi_definition, 
             pnl_type: _item.pnl_type, 
             kpi_datatype: _item.kpi_datatype,
             RowCreationDate: Today()
            }
        )
    )
)    

[Anonymous]

Thanks so much for this. You saved my day 🙂

[RandyHayes]

@Anonymous 

Always happy to save a day 😁