Update Gallery CheckBox

[iwonder]

Thank you for taking the time to read my question.

I asked this one earlier and thought that I understood the answer but after a few days I'm still stumped.

What I have is a Gallery that has a checkbox and a label for each record.

Gallery.Items = Filter('[dbo].[MedicationActive]',MedicationFacility = SelectedFacility.Text Or MedicationFacility = "All", MedicationLine = SelectedLine.Text Or MedicationLine = "All").Medication

Gallery.TemplateFill = If(ThisItem.Medication in colPelMed.Med,LightBlue,Transparent)

Gallery.OnCheck = If(ThisItem.Medication = "NONE",RemoveIf(colPelMed,Facility=SelectedFacility.Text,Line=SelectedLine.Text,Med<>"NONE"),RemoveIf(colPelMed,Facility=SelectedFacility.Text,Line=SelectedLine.Text,Med="NONE"));//update the collection
Collect(colPelMed,{Facility:SelectedFacility.Text,Line:SelectedLine.Text,Med:ThisItem.Medication});//add the currently selected item to the collection

IMPORTANT: colPelMed holds more than one record. It holds records for other Facility/Line combinations so I can't ClearCollect this collection. I have an unknown number of Facility/Line combinations so I can't create a unique collection for every combination.

What I want to do is if "NONE" is selected, then I want to clear all the other check boxes. If any of the other checkboxes are selected, then "NONE" should be cleared. Also, if there is no record found in the colPelMed then "NONE" should be selected to start, otherwise check the correct checkboxes. The reason I need to do this is because the user can start a record but not complete it. They may need to switch to another Facility/Line, update that record a bit more, then come back. Therefore I need to set this gallery to where they left off. 

I have a second gallery I use to test with. It shows what's currently in colPelMed filtered for Facility and Line. When I use the RemoveIf() function I can see this gallery refresh correctly so I know that my OnCheck formula is ok. I have not written my OnUnCheck yet as I want to get the OnCheck to work first... baby steps.

Any help would be great. I've been working on this for days.

Thanks

[RandyHayes · Accepted solution]

@iwonder 

Are you using the formula I provided on the Checkbox Default?

LookUp(colPelMed, 
       Med=Self.Text && 
       Facility=SelectedFacility.Text &&
       Line = SelectedLine.Text,
       true
)

[iwonder · Accepted solution]

Was just getting there.

Here is what I had and your code:

//If(ThisItem.Medication in LookUp(colPelMed,And(Facility=SelectedFacility.Text,Line=SelectedLine.Text),Med),true,false);
LookUp(colPelMed, 
       Med=Self.Text && 
       Facility=SelectedFacility.Text &&
       Line = SelectedLine.Text,
       true
)

With your code, when I click on NONE it checks, then when I click on "Am", "NONE" remains checked. with my code, when checking "Am", "NONE" becomes unchecked.

I also noticed if I, for example, select items further to the right, say Mx, Pp, Pu... when I check NONE, the first one always becomes unchecked. In this case Mx becomes unchecked.

[iwonder]

I should have also said that I do see the test gallery reflecting correctly. When I select NONE, the test gallery shows NONE, when I select Am, I see Am in the test gallery and NONE is not in the test gallery. so not really sure why the LookUp() is acting like that.

[iwonder]

I got your formula to work!!!!

LookUp(colPelMed, Med=ThisItem.Medication && Facility=SelectedFacility.Text && Line = SelectedLine.Text, true)

Changed the green part. NOW IT'S WORKING!!!!!!!! So many days spent on this.

Thank you thank you thank you. I can't say it enough! Thank you.

have a great day

[iwonder]

I am so excited I forgot to ask, why does your formula work and what I had does not?

[RandyHayes]

@iwonder 

Was just about to ask what the text property was of your checkbox.  I had assumed you were using the item value as your text property...apparently not.

[RandyHayes]

@iwonder 

The reason yours did not work was because you weren't putting the Med column as part of your criteria in the lookup.

Lookup returns the first record where the criteria is true.  Your formula only had criteria for Facility and Line.  So, that lookup would have returned ONE record based on that formula, then it was returning from that the Med column.

    LookUp(colPelMed,And(Facility=SelectedFacility.Text,Line=SelectedLine.Text),Med) 

So, ultimately, if you had 4 check items in your collection, the above formula would have only returned the first one.  And then you were setting the Default based on if ThisItem.Medication was in the one Med column.

Your formula would have worked if it was:

   ThisItem.Medication in Filter(colPelMed, Facility=SelectedFacility.Text && Line=SelectedLine.Text).Med

The reason is, in the above, the Filter would return a Table of all the items that match the Facility and Line.  The in operator would then look to see if the Medication was in the table in the Med column.  That would have also worked.

My formula was just a little more direct.  It said, lookup the Facility, Line and Med and return the column true.  This means that if it finds a record, it will return true.  If not, then it would be false.  This then was the default of your checkbox.

[iwonder]

Got it!

Thank you so much.

[RandyHayes]

@iwonder 

Also, if you could go through the post and mark the answer that best helped with the problem as "accept solution", that would be great.  Your question is not uncommon and when others come to the forum to find an answer to that same question, they will find the solution quicker.

[iwonder]

That's strange.... I did. I marked your LookUp formula post as the answer. 

Did it again just in case.