Show 1 Item in Gallery View with multiple occurrences within datasource

[forbudt4u]

Hello Community,

I have a gallery named BulkAddAssetGallery which I currently have the items set as _BulkAddCart.  Within the _BulkAddCart datasource I may have multiple of the same model # within the datasource, but with unique identifiers such as a Barcode # or Serial # for each record.  However, when it comes specifically to the gallery view, I only want the gallery to show a single occurrence of a Model # if there are multiples involved.  I would typically do a GroupBy function to accomplish this goal, but I can't do this because I can no longer use the ThisItem.Whatever to show specific information (such as the Barcode or Serial #) when drilling down to a specific record in the gallery.  Is there a way to do this in the gallery view using a dynamic filter of some sort?   Below is a small example of what my datasource may look like with 16 Records. If you key into Model #'s 2767-22 (Impact), 2853-20 (Impact) and CP200 XLS (Radios) you can see I have multiples of those.  In my gallery, I need to just show one occurrence of those records with all the other single occurrences of other model #'s as I will be utilizing a Quantity Label  with CountRows(Filter(_BulkAddCart,Model=ThisItem.Model)) to annotate how many of each item there is. 

ScanTag.	Category	Manufacturer	Model	Serial.	Cost	Holder
A18B-12786	AED	Phillips	Phillips HeartStart On-Site AED	A18B-12786	$1,000.00	Test Sup
406395000	Demolition Hammer	Bosch	(0)611304034	N/A	$1,205.00	Test Sup
404721651	Demolition Hammer	Hilti	TE700-AVR	3359	$1,239.00	Test Sup
404721672	Impact	Milwaukee	2753-20	N/A	$182.00	Test Sup
404722041	Impact	Milwaukee	2760-20	H16AD194104911	$329.00	Test Sup
401204707	Impact	Milwaukee	2767-22	Need Serial #	$405.00	Test Sup
406116431	Impact	Milwaukee	2767-22	HD6AD195110275	$405.00	Test Sup
10004A86	Impact	Milwaukee	2853-20	Faded Off	$127.00	Test Sup
407418731	Impact	Milwaukee	2853-20	J76AF213801695	$127.00	Test Sup
404721644	Impact	Milwaukee	9075-20	239DD171400317	$270.00	Test Sup
404721643	Impact	DeWalt	DW292	N/A	$153.00	Test Sup
2877	Radio	Motorola	CP200	807TVV2877	$1,219.00	Test Sup
4940	Radio	Motorola	CP200 XLS	807TUX4940	$1,219.00	Test Sup
7837	Radio	Motorola	CP200 XLS	018TPM7837	$1,219.00	Test Sup
7804	Radio	Motorola	CP200 XLS	018TPM7804	$1,219.00	Test Sup
D327	Radio	Motorola	CP200d	807TUMD327	$1,219.00	Test Sup

The end result would be a gallery that looks like this (only 12 items, but actually based on the 16 records above with multiples accounted for):

Category	Manufacturer	Model	Cost	Holder	Qty
AED	Phillips	Phillips HeartStart On-Site AED	$1,000.00	Test Sup	1
Demolition Hammer	Bosch	(0)611304034	$1,205.00	Test Sup	1
Demolition Hammer	Hilti	TE700-AVR	$1,239.00	Test Sup	1
Impact	Milwaukee	2753-20	$182.00	Test Sup	1
Impact	Milwaukee	2760-20	$329.00	Test Sup	1
Impact	Milwaukee	2767-22	$405.00	Test Sup	2
Impact	Milwaukee	2853-20	$127.00	Test Sup	2
Impact	Milwaukee	9075-20	$270.00	Test Sup	1
Impact	DeWalt	DW292	$153.00	Test Sup	1
Radio	Motorola	CP200	$1,219.00	Test Sup	1
Radio	Motorola	CP200 XLS	$1,219.00	Test Sup	3
Radio	Motorola	CP200d	$1,219.00	Test Sup	1

[Drrickryp · Accepted solution]

@forbudt4u 

For the Items property of the gallery:

Sort(GroupBy(_BulkAddCart,"Model","DATA"),Model)

In the gallery for each label:

• ThisItem.Model
• First(ThisItem.DATA).Manufacturer,
• First(ThisItem.DATA).Cost
• First(ThisItem.DATA).Holder
• "Qty: "& CountRows(ThisItem.DATA)

[forbudt4u]

@Drrickryp 

Appreciate it, this worked like a charm.  If you don't mind, in a brief summary, can you explain what the .DATA portion of this is doing so I understand?

[Drrickryp]

@forbudt4u 

Sure, The GroupBy function returns a table with records grouped together based on the values in one or more columns. Records in the same group are placed into a single record, with a column added that holds a nested table of the remaining columns.  The format of the function is GroupBy(table,"OneColumn","RestOfData")

The first element inside the "" must be the actual name of a column that will group the data. The last element of the function is the nested table.  Other functions like First(ThisItem.DATA).columnname will retrieve the first record in a column of the nested table.  Aggregate functions like Sum(), Average() and CountRows() can be used on the nested table to retrieve values from that table. 

[forbudt4u]

@Drrickryp 

This is beautiful, because I have a couple of Manager Portals in my app where I essentially do this same thing, but with people rather than Model #'s.  To summarize, I used a whole bunch of collections to gather data and referenced those collections.  Now that I can do this, I can used the nested tables and pull the same data with a code that's MUCH shorter and easier to manage.  Thank you so much for explaining!!!

[Drrickryp]

[forbudt4u]

@Drrickryp 

In attempting to add more features to my app, I think I've uncovered a potential issue with one of the features I need.  What I want to do is put an overlay text input box directly over the rows in my gallery and make it's visible property to true if that specific row/record is empty.  However, when I attempt to do this, I do not have to .AllItems in my list of options for the said gallery, so I cannot reference specific row numbers.  Are there no row numbers assigned to this nested table?  

[Drrickryp]

@forbudt4u 

Sounds like a new question. 

[forbudt4u]

@Drrickryp 

Sounds good.  I think I'll hold off as I believe I already found the answer in another thread (no, nested tables do not have row numbers, but you can force row numbers with code).  Anyways, I found a better solution in just overlaying blank text inputs  on top of the gallery rows and then setting the fill to white should I place anything in the input... works out great.  Thanks again for your help.