cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahmoodhsaud
Helper IV
Helper IV

How to do a sequential add and patch in dataverse

Hi Everyone,

I'm having a two tables in dataverse Table1 and Table 2 respectively, I wanted to take the values from Table1 and patch it to table 2. In Table2 I want to add the values of (Group sequence, Account Sequence) from table1 by 1 and the set the max value to 1.

Table 1 -> Table 2

Group sequence = Group sequence + 1

Account Sequence =  Account Sequence + 1
Max of Sub Group Sequence = 1
Max of Account Sequence = 1

Table 1 

mahmoodhsaud_0-1626696047174.png

Table 2

mahmoodhsaud_1-1626696530903.png

 

Thanks in Advance,

Regards,

Mahmood

 

10 REPLIES 10
mahmoodhsaud
Helper IV
Helper IV

Hi @dpoggemann  , Can you help me out with this

Regards,

Mahmood

CNT
Super User
Super User

@mahmoodhsaud What does this mean? Could you explain it a bit more clearer?

Max of Sub Group Sequence = 1
Max of Account Sequence = 1

@CNT

I wanted to get the max value of new Account sequence and new sub group sequence.

In the table if you see theres sub group sequence and new account sequence which is based new sub group sequence.

Ex new sub group 1 has new account sequence 1,2,3 and I wanted to take the max of the new account sequence which is 3 similarly for the others also and set the max value in the new account sequence to 0. Similarly also get the max value of new  sub group and set it to zero

mahmoodhsaud
Helper IV
Helper IV

hi @CNT  any updates?

@mahmoodhsaud I don't see sub group sequence or new sub group sequence anywhere in your table! I'm not sure what you are referring to.

@CNT ,

 

Below Table (Table1) is the source table

Table 1

mahmoodhsaud_0-1627525869034.png

 

Take the value from table 1 and patch it to table 2,  Set Column

New Group Sequence = Group Sequence + 1

New Account Sequence = Account Sequence + 1

 

Table 2 

mahmoodhsaud_1-1627526123781.png

 

Find the max value in column "New Group Sequence" and "New Account Sequence" and set it to 1.

In Column "New Group Sequence", we will get more than one max value based on the Column "New Account Sequence". For ex, Austin's value is set to 1 and also fred's value is set to 1 as both of them has different "New Account Sequence" value.

 

 

Table 2

mahmoodhsaud_2-1627526341385.png

 

Regards,

Mahmood

 

 

 

@CNT , need to do the logic either in power apps or power automate

@mahmoodhsaud Try this,

ForAll(Table1 As SourceTable,
	Patch(Table2, Defaults(Table2),
		{
			'Account Name': SourceTable.'Account Name'
			'Group Sequence': SourceTable.'Group Sequence'
			'Account Sequence': SourceTable.'Account Sequence'
			'New Group Sequence': Value(SourceTable.'Group Sequence')+1
			'New Account Sequence': Value(SourceTable.'Account Sequence')+1
		}
	)
);
With(
	{varMaxNewGroupSequence:AddColumns(GroupBy(Table2, "New Account Sequence" ,"NewAccountSequenceGroup"),"MaxNewGroupSequence", Max(NewAccountSequenceGroup,'New Group Sequence'))},
	ForAll(NewAccountSequenceGroup As NewGroup,
		Patch(Table2, LookUp(Table2,'New Account Sequence'=NewGroup.'New Account Sequence' && 'New Group Sequence'=MaxNewGroupSequence),
			{
				'New Group Sequence': 1
			}
		)
	)
);
With(
	{varMaxNewAccountSequence:First(Sort(Table2, 'New Account Sequence', Descending)).'New Account Sequence'},
	UpdateIf(Table2, 'New Account Sequence'=varMaxNewAccountSequence,
		{
			'New Account Sequence': 1
		}
	)
);

@CNT  Thanks for your valuable inputs,

 

There are some error at the output. 

ForAll(
    Filter(
        M_stratCol,
        StrategyId = "1"
    ) As SourceTable,
    Patch(
        T_statCol,
        Defaults(T_statCol),
        {
            'Account Name': SourceTable.'Account Name', 
            'Last Account Sequence': SourceTable.'Last Account Sequence',
            'Last Sub Group Sequence': SourceTable.'Last Sub Group Sequence',
            'New Account Sequenc': Value(SourceTable.'Last Account Sequence') + 1,
            'New Sub Group Sequence': Value(SourceTable.'Last Sub Group Sequence') + 1
        }
    )
);

With(
    {
        varMaxNewGroupSequence: AddColumns(
            GroupBy(
                T_statCol,
                "new_newaccountsequenc",
                "NewAccountSequenceGroup"
            ),
            "MaxNewGroupSequence",
            Max(
                NewAccountSequenceGroup,'New Sub Group Sequence'
                
            )
        )
    },
    ForAll(
        varMaxNewGroupSequence As NewGroup,
        Patch(
            T_statCol,
            LookUp(
                T_statCol,
                'New Account Sequenc' = NewGroup.new_newaccountsequenc && 
                'New Sub Group Sequence' = NewGroup.MaxNewGroupSequence
                 
            ),
            {'New Sub Group Sequence': 1}
        )
    )
);

With(
	{varMaxNewAccountSequence:First(Sort(T_statCol,'New Account Sequenc', Descending)).'New Account Sequenc'},
	UpdateIf(T_statCol, 'New Account Sequenc'=varMaxNewAccountSequence,
		{
			'New Account Sequenc': 1
		}
	)
);

 

The "New Sub Group Sequence" Column is not updating properly its only taking the max value in the column instead of taking values based on the "New Account Sequence" column.

 

And "New Account Sequence" is also not updating correctly. Below is the snippets of the error

mahmoodhsaud_0-1627883344514.png

Regards,

Mahmood

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,859)