cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
epiej
Level: Powered On

Patching from two collections to one

I have two collections, SOURCE_1 and SOURCE_2. When I add a record to SOURCE_1, it's info is duplicated to SOURCE_2 and the ID's of each collection are generated automatically. I'm trying to copy the last X number of new record IDs from the two collections into a destination collection, paring each id 1-to-1. Say the below are the most recently created record ID's in each collection. I'd like to combine them in the destination ala:

 

SOURCE_1_ID  SOURCE_2_ID
10 20 11 21 12 22 13 23 14 24

 

The number of new records to create in the destination is equal to the number of new records created in sources 1 and 2. On the patch(below), however, it's thowing an error, saying it expected type number but found type table for SOURCE_1_ID. I tried displaying the TempIDCollection on a gallery, but it's also having an error, saying it's expecting text or a number. Below is my current logic.

 

Collect(TempIDCollection,
{Source_1_ID: LastN(SOURCE_1, X).SOURCE_1_ID,
Source_2_ID: LastN(SOURCE_2, X).SOURCE_2_ID});

ForAll(TempIDCollection,
          Patch(DESTINATION, Defaults(DESTINATION),
          {SOURCE_1_ID: Source_1_ID,
           SOURCE_2_ID: Source_2_ID})
)

 

5 REPLIES 5
Super User
Super User

Re: Patching from two collections to one

@epiej 

I am assuming you are stating that you wish to create 5 records in the DESTINATION datasource.

If so, then consider this formula instead:

ForAll([5,4,3,2,1],
   Patch(DESTINATION, Defaults(DESTINATION),
        {
SOURCE1_ID: First(LastN(SOURCE_1, Value)).SOURCE_1_ID, SOURCE2_ID: First(LastN(SOURCE_2, Value)),Source_2_ID
}
) )

As often, no need for a Collection.

See if that helps you out.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
epiej
Level: Powered On

Re: Patching from two collections to one

@RandyHayes 

I need to rephrase. The number of new records to create could be various. Could be 5, could be 500.

Super User
Super User

Re: Patching from two collections to one

@epiej 

Rephrased and re-edited I see...very good.

 

Okay, so then in that case give this formula a spin:

Clear(DESTINATION);

ForAll(SOURCE1,
   Collect(DESTINATION, 
        {
         Source_1_ID: Source_1_ID,
         Source_2_ID: Last(FirstN(SOURCE_2, CountRows(DESTINATION)+1)).Source_2_ID
        }
     )
)

You will need to create the DESTINATION collection (or whatever you want to call it) in order to make this happen.

After that, you can execute the Patches to your datasource:

ForAll(DESTINATION,
    Patch(yourDataSource, Defaults(yourDataSource),
         {Source_1_ID: Source_1_ID,
Source_2_ID: Source_2_ID
}
)
)

I hope that gives you what you need.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
epiej
Level: Powered On

Re: Patching from two collections to one

@RandyHayes 

I'm afraid it doesn't. Your fucntion seems to take all of the records in SOURCE_1, when I just want the newly created records.

 

When I push a button, the new records are created in each source, no problem there. I want only those new records to go to the datasource.

Super User
Super User

Re: Patching from two collections to one

@epiej 

You don't have sufficient abilities within your collection to determine what is new and what is not.  There is no way to determine, as your collections are now, if something is new.

However, with some changes, you could do this.  Here's the concept:

Each record will need some sort of flag to indicate if it is new or used.  If you have an additional column in SOURCE1 and SOURCE2, let's call it "used", then you can do something like this:

Clear(DESTINATION);

ForAll(Filter(SOURCE1, !used),
   Collect(DESTINATION, 
        {
         Source_1_ID: Source_1_ID,
         Source_2_ID: Last(FirstN(Filter(SOURCE_2, !used), CountRows(DESTINATION)+1)).Source_2_ID
        }
     )
);
UpdateIf(SOURCE1, !used, {used:true});
UpdateIf(SOURCE2, !used, {used:true})

Beyond that, there is no way to determine from a collection of single column numbers what is new...in fact, new since what? - there is nothing that would mark it as such, unless you use something like above.

 

EDIT:

Actually, I amend this post.  I thought of another way to do it as well.  You could capture at the point that you execute the function, the number of rows in the SOURCE1 collection and use that in the formula each time.

Formula looks like this:

Clear(DESTINATION);

ForAll(LastN(SOURCE_1, CountRows(SOURCE_1)-lastCount),
   Collect(DESTINATION, 
       {
        SOURCE_1_ID: SOURCE_1_ID,
        SOURCE_2_ID: Last(
FirstN(
LastN(SOURCE_2, CountRows(SOURCE_1)-lastCount),
CountRows(DESTINATION)+1)
).SOURCE_2_ID } ) ); Set(lastCount, CountRows(SOURCE_1))

That would avoid having to alter the source collection and would give you some way to know what is new.

See if that is a charm for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 170 members 5,561 guests
Please welcome our newest community members: