Patching from two collections to one

[epiej]

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})
)

[RandyHayes]

@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.

[epiej]

@RandyHayes 

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

[RandyHayes]

@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.

[epiej]

@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.

[RandyHayes]

@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.