How to merge two collections with different columns and records

[raphansa]

Hi there,

I'm trying to merge two completely different collections (collectionA and collectionB) with different records and columns into a new collection (newCollection). I've tried creating a new collection (newCollection) using AddColumns to collectionA but the records from collectionB were merged in different rows. As a result I am expecting a newCollection with the same columns from collectionA and collectionB with the exact same structure for the records from collectionA and collectionB.

Here is an image that shows clearly what I'm looking for:

Here is an example.

collectionA:

ID	Name	LastName	Status
1	Julius	Bennet	approved
2	Nick	Sales	removed
3	Maria	Torres	pending
4	Sam	Menning	approved

collectionB:

Order Name	Price	Quantity
tshirt floral P	20	3
coat black M	45	2
tshirt white P	20	1
cap green U	15	4

newCollection - expected result:

ID	Name	LastName	Status	Order Name	Price	Quantity
1	Julius	Bennet	approved
2	Nick	Sales	removed
3	Maria	Torres	pending
4	Sam	Menning	approved
				tshirt floral P	20	3
				coat black M	45	2
				tshirt white P	20	1
				cap green U	15	4

Thanks for the help! 🙂

[RandyHayes]

@raphansa 

If you want what you showed in your expected results example, then you can simply do:

   ClearCollect(NewCollection, CollectionA, CollectionB)

I hope this is helpful for you.

[CNT]

@raphansa 

Please try the following,

ClearCollect(newCollection, 
	ForAll(Sequence(CountRows(collectionA)),
		({
			ID:First(LastN(collectionA,ThisRecord.Value)).ID, 
			Name:First(LastN(collectionA,ThisRecord.Value)).Name, 
			LastName:First(LastN(collectionA,ThisRecord.Value)).LastName,
			Status:First(LastN(collectionA,ThisRecord.Value)).Status, 
			OrderName:First(LastN(collectionB,ThisRecord.Value)).OrderName, 
			Price:First(LastN(collectionB,ThisRecord.Value)).Price, 
			Quantity:First(LastN(collectionb,ThisRecord.Value)).Quantity, 
		})
	)
)

[raphansa]

Hi!

Thanks for the answer! I've tried using this formula, but it shows an error of "Invalid arguments", I assume that ClearCollect items cannot be collections.

[raphansa]

Hey @CNT !

Thanks for the answer! But what if collectionA and collectionB had different number of rows, would this formula still work?

[RandyHayes]

@raphansa 

With the formula ClearCollect(NewCollection, CollectionA, CollectionB) you will end up with what you were looking for regardless of the number of rows.

[raphansa]

@RandyHayes 

I've just tried using this formula but I'm receiving an error of "Invalid arguments" here is a photo.

Translating the error message:

"The types provided are incompatible.

The "ClearCollect" function has some invalid arguments."

Thanks! 🙂

[RandyHayes]

@raphansa 

Place your pointer directly over the dark red underlined promotions and see what the error shows.

The formula is perfectly fine, so there has to be something about the promotions part.

Using your provided sample data...this formula:

Produces these results in colC (sorry a bit cut off, but shows the results):

[raphansa]

@RandyHayes 

I don't know what might be causing this, but for me it shows the same error message as before ("Invalid arguments"). If I change the order the error remains the same 😞

-- Changing the order:

[RandyHayes]

@raphansa 

Something is conflicting in some other part of the formula or some other formula in your app.

The best thing to do at this point is to comment out the clearcollect statement in this formula and then look at the other two collections to see what they contain and what they are used for.

I am not sure what you are using these for, or why even a collection is needed, but that would be the first place to look is at every place you are using the collection and how you are populating the values to it.  If you have more examples, I can look at those too.