Need a help in handling nested collection and show values in Gallery

[venka91]

Hello there,

I have Collection, where it comes from 3 different list.

masterlist

preglist

calflist

I would like to show the history of Insemination, Pregnancy and Calving history of cow in a gallery.

So OnSelect of Button i have the below forumula.

Set(varViewCowRec = Filter(masterlist, Title = txt_idnumber.text));

ClearCollect(InsemiCollection,
With(
    {
        wInsemi:
        Filter(InseminationList,insemi_tag = varViewCowRec.Title)
    },

    AddColumns(wInsemi,"PregHistory",Filter(CowPregList, Title = varViewCowRec.Title),"CalvingHistory",Filter(calfList,Title = varViewCowRec.Title))));

Now In Gallery, i have given Item property as InsemiCollection

My gallery has 3 text boxes to show the Insemination Date, Pregnancy Date and Calving date for the cow.

Insemination Text has the property : 

Text(ThisItem.insemination_date,"dd/mm/yyyy")

This shows all the dates properly.

However when i try to write formula for Pregnancy and calving history, i am stuck here, as i could not extract a column from my collection, as it comes as table.

Any idea Please, ?  i was trying to search it, but confused.

[metsshan]

Are your lookups to CowPregList and calfList expected to only return one result? If so, then you can change to using LookUp instead of Filter, and your "ThisItem.PregHistory.preg_date" expression should be valid:

AddColumns(
   wInsemi,
   "PregHistory", LookUp(CowPregList, Title = varViewCowRec.Title),
   "CalvingHistory", LookUp(calfList, Title = varViewCowRec.Title)
)

[phipps0218]

Concat(ThisItem.PregHistory, preg_date, " , ")

Please Accept as Solution if it solves your question. Or just give it a Thumbs Up if it is helpful because this can help others.

LinkedIn: https://www.linkedin.com/in/charlie-phipps-%F0%9F%91%A8%E2%80%8D%F0%9F%92%BB-91338715b/
YouTube: https://www.youtube.com/channel/UChmFBGU1YKIU91sNMQ7buGg
Twitter: https://twitter.com/phipps0218

[venka91]

No , preghistory and calving history has multiple records for a cow 

[metsshan]

Is a Pregnancy, and subsequently a Calving, tied together by a common identifier?

ie. does your PregList reference the Insemination Date?

[venka91]

All preglist and calvinglist has the Title common.

[venka91]

All preglist and calvinglist , masterlist has the Title common.

Also i have the reference I'd column in insemination , preg and calf list. 

When i cow is added in master list , the insemination , preg and calf detailes needs to be entered by giving the cow I'd which is same across all tables. 

[metsshan · Accepted solution]

To display this in a Gallery, you would need a way to tie an Insem to a Preg to a Calf, additional to them being for the same Cow, so that you could create a Table like this for your Gallery:

Which would need source data that looks like this (see how each table references the other):

Then we could do a proper join of the tables, it would be a 1 to 1 join, though, which makes me wonder why you don't just have one SharePoint list to begin with? The biology of the events seem to tie them together consecutively...

However, if you just fetch the three tables separately, there is no need for a gallery if you are only showing information about one cow at a time. I will demonstrate using Auto-Height Labels:

My button OnSelect to create test data:

ClearCollect(
    colInsem,
    {Cow: "Cow1", InsemDate: DateAdd(Now(), -1000, Days)},
    {Cow: "Cow1", InsemDate: DateAdd(Now(), -500, Days)},
    {Cow: "Cow1", InsemDate: DateAdd(Now(), -200, Days)},
    {Cow: "Cow2", InsemDate: DateAdd(Now(), -200, Days)},
    {Cow: "Cow2", InsemDate: DateAdd(Now(), -50, Days)}
);

ClearCollect(
    colPreg,
    {Cow: "Cow1", PregDate: DateAdd(Now(), -998, Days)},
    {Cow: "Cow1", PregDate: DateAdd(Now(), -447, Days)},
    {Cow: "Cow1", PregDate: DateAdd(Now(), -199, Days)},
    {Cow: "Cow2", PregDate: DateAdd(Now(), -1, Days)}
);

ClearCollect(
    colCalf,
    {Cow: "Cow1", CalfDate: DateAdd(Now(), -750, Days)},
    {Cow: "Cow1", CalfDate: DateAdd(Now(), -350, Days)}
);

Insem Label (Auto-Height) Text:

Concat(
    SortByColumns(Filter(colInsem, Cow = TargetCow), "InsemDate", Ascending), 
    Text(InsemDate, "dd/mm/yyyy"), 
    Char(10)
)

Preg Label (Auto-Height) Text:

Concat(
    SortByColumns(Filter(colPreg, Cow = TargetCow), "PregDate", Ascending), 
    Text(PregDate, "dd/mm/yyyy"), 
    Char(10)
)

Calf Label (Auto-Height) Text:

Concat(
    SortByColumns(Filter(colCalf, Cow = TargetCow), "CalfDate", Ascending), 
    Text(CalfDate, "dd/mm/yyyy"), 
    Char(10)
)

Does this help you achieve your goal?

[venka91]

I think, i have done the same way. 

Yes, on Button click, i have created a collection , with the filter, But you are creating filters at the text box level.

But unfortunately, the Concat function not giving me the expected output.

My text control has property as : 

Concat(ThisItem.PregHistory.preg_date,"")

No error displayed, however my gallery does not give me the data.

To answer why i did not out all in same table, because each table has few more information to be captured. This makes the field more on my list. So i just drilled down to tables with fewer columns.

[metsshan]

I don't think the Concat() is going to give you what you are looking for, but try this to see what you get:

Concat(ThisItem.PregHistory, preg_date, Char(10))