Find? If? True and False for finding Data.

[KRider]

Hey everyone,

back again with a stumper! I can't figure

Here is my code:

If(
    IsBlank(
        Find(
            Concatenate(
                First(col_currentview).TMMID,
                ";",
                First(col_currentview).TMName,
                ";",
                First(col_currentview).TMShift,
                ";",
                "NotTrained"
            ),
            ThisItem.Sign_data_A
        )
    ),
    "Not Trained",
    "Trained"
)

Here is the exampled string from my sharepoint list I am searching in:

250136;Aldrin Belonio;A;Trained;8/31/2022;270694;Angel Davie;A;NotTrained;8/30/2022;530278;Aracely Canales;A;NotTrained;8/30/2022;557916;Christina Hawco;A;NotTrained;8/30/2022;531792;Cole Shelly;A;NotTrained;8/30/2022;272810;Jessie Sansait;A;NotTrained;8/30/2022;258500;Kandi Arbour;A;NotTrained;8/30/2022;248665;Robert Diep;A;NotTrained;8/30/2022;257592;Barry Kirk;B;NotTrained;8/30/2022;250511;Jamie Drake;B;NotTrained;8/30/2022;429381;Jordan Fitzpatrick;B;NotTrained;8/30/2022;448955;Quinton Robertson;B;NotTrained;8/30/2022;565440;Thomas Tweedle;B;NotTrained;8/30/2022;249132;Jesse Whitty;B;NotTrained;8/30/2022;

[KRider]

This is to avoid delegation errors, I deleted the variable Set(var_DCR_view, First(col_currentview).TMProcess);

and it popped up a delegation error. caching the collection is how I eliminated those warnings. 

Same with the table collection. I collect the single record to avoid lots of delegation errors.

[KRider]

Maybe I don't get it... It's ok to have delegation warnings?

[KRider]

The collection only ever stores 1 item. It's the current Process Certification we are looking at.

We are taking the Team Member Name, Number, and Shift from this collection and using Concatenate to create a search string. 

Then I want to see if that string is true or false in the command_DCR_database to show whether the Team Member was trained in this change point.

[KRider]

"Trained: " & 
If(
    //The Concatenate items are from this collection
    Concatenate(
        First(col_currentview).TMMID,
        ";",
        First(col_currentview).TMName,
        ";",
        First(col_currentview).TMShift,
        ";",
        "NotTrained"
    ) in 
    //It builds the string to search in this database. 
    LookUp(
        command_DCR_database,
        'DCR Number' = ThisItem.Sign_data_A,
        Sign_data_A
    ),
    "Not Trained",
    "Trained"
)

[RandyHayes]

@KRider 

Okay then, if that collection has only ever one record in it (which is not a use for a Collection!) then what is the Items property of your Gallery?

I was under the impression that col_currentview was a table that was used for the items table of the gallery!!??

Is col_current_dcr the Items of your Gallery?

The col_currentview collection is based on a Filter, so it is going to have a table, not just a record.  However, are you stating that your Filter:

    Filter(command_process_certification, Title = varSelectedCert) 

is only ever going to return ONE record?

But, before I jump into more of it...let me know what is the Items property on the Gallery so I can make more sense of how you have things together.

[KRider]

Instead of using a Collection for the 1 item I use, should I switch that to a variable?

This is my Items to show all DCR's for the Process under the Team Member Certification: 

Sort(
    Filter(
        command_DCR_database,
        'DCR Name' = var_DCR_view
    ),
    'DCR Number',
    Descending
)

col_current_dcr is for use in another screen where the Team Members sign to be certified for the changes made.

And yes, it is my intention for BOTH collections to only ever have information from each and then to patch information back into the list once I am done with it.

[RandyHayes]

@KRider 

Pieces are starting to come together!

I don't want to tear apart so much (tempting to do as there is a lot to change around), but I want to find where all the sources of information are coming from to give you what you need to answer your original question.

Rather than hit you with a long list of questions, I will ask one-by-one as some answers will impact my next question...please be patient as I go through this.

Okay - on this screen:

You are stating that "When they select a cert" - where are the selecting that on this screen?  Is it from the left list?  Is that a Gallery?  If so, what is the name of that Gallery?  And what is the Items property of the gallery?  If that Items property is a collection, then how are you assigning records to that collection?

[KRider]

Ok, so here the Team leader will select the line they are on, the shift and whether they want to start from Process or Team Member. In the picture above the List on the Left selects the team member, the gallery on the right then loads the processes that TM knows.

So both are galleries of information. The Team Members are read from our HR database, the Processes are read from the Process database. The Document Change Request(DCR) is read from the DCR database.

I only use the collection to hold the record we are looking at.

When you click an item on the Right gallery this is the code:

ClearCollect(
    col_currentview,
    Filter(
        command_process_certification,
        Title = varSelectedCert
    )
);
Concurrent(
    Set(varSelectedCert, Trim(Last(FirstN(Split(ThisItem.Result, ";"),2)).Result)),
    Set(varCert, "View"),
    Set(var_DCR_view, First(col_currentview).TMProcess),
    Set(varsignoff, "Off")
);
Navigate(proc_view,ScreenTransition.Cover)

The Items for the right gallery is:

If(
    dpdn_tmp.SelectedText.Value = "Team Member",
    Distinct(
        Filter(
            command_process_certification,
            TMName = gly_tm_proc.Selected.Result
        ),
        TMProcess & "; " & Title
    ),
    dpdn_tmp.SelectedText.Value = "Process",
    If(
        dpdn_shift.SelectedText.Value = "All",
        Distinct(
            Filter(
                command_process_certification,
                TMProcess = gly_tm_proc.Selected.Result
            ),
            TMName & "; " & Title
        ),
        Distinct(
            Filter(
                command_process_certification,
                TMProcess = gly_tm_proc.Selected.Result,
                TMShift = dpdn_shift.SelectedText.Value
            ),
        TMName & "; " & Title
        )
    )
)

The Gallery on the Left Is:

If(
    dpdn_tmp.SelectedText.Value = "Team Member",
    If(
        dpdn_line.SelectedText.Result = "All Lines",
        If(
            dpdn_shift.SelectedText.Value = "All",
            Sort(
                Distinct(
                    command_employee_list,
                    TMName
                ),
                Result,
                Ascending
            ),
            Sort(
                Distinct(
                    Filter(
                        command_employee_list,
                        TMShift = dpdn_shift.SelectedText.Value
                    ),
                    TMName
                ),
                Result,
                Ascending
            )
        ),
        If(
            dpdn_shift.SelectedText.Value = "All",
            Sort(
                Distinct(
                    Filter(
                        command_employee_list,
                        TMLine = dpdn_line.SelectedText.Result
                    ),
                    TMName
                ),
                Result,
                Ascending
            ),
            Sort(
                Distinct(
                    Filter(
                        command_employee_list,
                        TMLine = dpdn_line.SelectedText.Result,
                        TMShift = dpdn_shift.SelectedText.Value
                    ),
                    TMName
                ),
                Result,
                Ascending
            )
        )
    ),
    dpdn_tmp.SelectedText.Value = "Process",
    If(
        dpdn_line.SelectedText.Result = "All Lines",
        Distinct(
            command_WA_processes,
            'Process Name'
        ),
        Distinct(
            Filter(
                command_WA_processes,
                Title = dpdn_line.SelectedText.Result
            ),
            'Process Name'
        )
    )
)

[RandyHayes · Accepted solution]

@KRider 

Couple of things with that (and this is not related to the final solution...this is just issues with your formulas).

1) You are referencing the SelectedText property of your dropdown controls.  That is a deprecated property and should be replaced with the appropriate Selected.<columnName> instead.

2) Your left gallery items is working from two different lists.  This is usually a bad thing in an items property of any control, to have different sources used.  The only "saving" factor in this is that you are putting them all through Distinct, which will reduce them to the same schema.  But, it is still a red flag.

3) Given the point 1 above, you can refactor your formulas to be a little more maintainable and easier to read.

Items property for the right Gallery:

Distinct(
    Filter(command_process_certification,
        (dpdn_tmp.Selected.Value = "Team Member" && TMName = gly_tm_proc.Selected.Result) ||
        (dpdn_tmp.Selected.Value = "Process" && dpdn_shift.Selected.Value = "All" && TMProcess = gly_tm_proc.Selected.Result) ||
        (dpdn_tmp.Selected.Value = "Process" && TMProcess = gly_tm_proc.Selected.Result && TMShift = dpdn_shift.Selected.Value)
    ),
    If(dpdn_tmp.Selected.Value = "Team Member", TMProcess, TMName) & "; " & Title
)

Items property for the left Gallery:

Switch(dpdn_tmp.Selected.Value, 
    "Team Member",
        Distinct(
            Filter(command_employee_list,
	        (dpdn_shift.Selected.Value = "All" || TMShift = dpdn_shift.Selected.Value),
	        (&& dpdn_line.Selected.Result = "All Lines" || TMLine = dpdn_line.Selected.Result)
            ),
            TMName
        
        ),
    "Process"
        Distinct(
            Filter(command_WA_processes,
                (dpdn_line.Selected.Result = "All Lines" || Title = dpdn_line.Selected.Result)
            ),
            'Process Name'
        )
)

It appears that the left gallery is informational only as nothing so far that I see refers to it.

Your OnSelect for the right gallery can be the following:

Set(glbCurrentRecord, LookUp(command_process_certification, Title = Trim(Index(Split(ThisItem.Result, ";"), 2).Result))));

Set(varCert, "View");   // not sure of the use
Set(varsignoff, "Off");  // not sure of the use

Navigate(proc_view,ScreenTransition.Cover)

Not sure what the other variables are for, but left alone for now.

The glbCurrentRecord now becomes your primary record from the selection.  It can be referenced elsewhere.

Now, your Items property for the DCR gallery would become:

Sort(
    Filter(command_DCR_database,
        'DCR Name' = glbCurrentRecord.TMProcess
    ),
    'DCR Number', Descending
)

NOW...finally, the Label for Trained and Not Trained would be:

With(glbCurrentRecord,
    If(
        !($"{TMMID};{TMName};{TMSHIFT};Trained" in ThisItem.Sign_data_A), "Not "
    ) & "Trained"
)

Sorry it took so many questions...needed to review several times just to make sure I was picking up what you were putting down.

Let me know where you run into issues on the above (keeping in mind this is all hand-typed, so no error checking is available).

[KRider]

Randy I really want to thank you for not only showing me the answer but helping me learn about coding and how to make it as efficient as possible. Those variables control "pop-up" containers.