Preventing a user from adding an item to a gallery if an existing item has a specific status

[BrendanZ1]

Hi all,

I am currently trying to prevent users from adding an item to a gallery, if there is already an item in there with a matching title and the status is not equal to Complete.

This is essentially a job log, where the user selects a Work Order, then the type of work they are doing for that work order, and confirms their selection. This patches a record to a list (WOLogSheet), and then shows up in the gallery below. Then they can "clock-in" and out of the task as it is selected in the gallery.

My issue at the moment, is that users are still accidentally duplicating entries into the gallery e.g.

WO - 0001

Task - Sourcing material
Status - Available

Date - 12/5/20

WO-0001

Task - Sourcing material

Status - Available

Date - 12/5/20

What should happen, is that they clock in to the first entry, which automatically changes the status to Active. Then when they clock out, the status changes to Complete and then disappears from the gallery (set to filter out any entries with a status of Complete).

So I need to work out a way so that when they select a work order etc, if the gallery already has an item that matches, it will notify them that it already exists and stops the patch from running.

Here is my code for the "Confirm Selection" button that patches the new record to the WOLogSheet list (it pulls data from a master list).

Set(VarStockCode, LookUp(WorkOrderListMaster, WorkOrderID=ComboBox2.Selected.WorkOrderID)); If(!IsBlank(VarStockCode),( Patch(WOLogSheetNew, Defaults(WOLogSheetNew), {WorkOrderID: ComboBox2.Selected.WorkOrderID, Title: VarStockCode.Title, AssignedToEmail:VarStockCode.AssignedToEmail, TaskDescription:Dropdown2.Selected})));Reset(ComboBox2);Reset(Dropdown2)

I am guessing that I need to insert an If statement somewhere to test against any items in the gallery.

At the moment, I think it needs to test against the values in the ComboBox2 and TaskDescription - I imagine any statement like this is going to look at any records in the list, so it really needs to only see if the selection matches any gallery item with a status of Available or Active. This is because the user will log hours against the same work order and task description a number of times, therefore the list will have mutliple entries that look like this:

WO-0002

Task - Execution of task

Status - Complete

Date - 12/5/20

WO-0002

Task - Execution of task

Status - Complete

Date - 13/5/20

Thanks for any help

[WarrenBelz]

Hi @BrendanZ1 ,

Suggested syntax below - you will have to check the logic

If(
   !IsBlank(
       LookUp(
          WorkOrderListMaster, 
          WorkOrderID=ComboBox2.Selected.WorkOrderID
       )
   )&&
   IsBlank(                            //GALLERY TEST HERE
      Lookup(
         YourCollectionName,
         WorkOrderID=YourGalleryName.Selected.WorkOrderID
      )
   )
   Patch(
      WOLogSheetNew, 
         Defaults(WOLogSheetNew), 
         {
            WorkOrderID: ComboBox2.Selected.WorkOrderID, 
            Title: VarStockCode.Title, 
            AssignedToEmail:VarStockCode.AssignedToEmail, 
            TaskDescription:Dropdown2.Selected
         }
      )
   ),
   Notify("Record Exists . . . . ")
);
Reset(ComboBox2);
Reset(Dropdown2)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[BrendanZ1]

Hi @WarrenBelz ,

I thank you for taking the time to have a look, but I am not sure how to start using the syntax you have provided due to my inexperience sorry.

To be honest, I still don't have a sound understanding of what every aspect of my existing code does - other than the basics such as that it essentially copies values from one list and pastes them into another, provided the WorkOrderID exists in the Master list....

I am still getting my head around variables and I am sure they are easy to understand, but finding the time to test and lock that knowledge away is a struggle as I need to keep developing solutions on the fly for work.....

In plain english, I see the test currently being:

If the WorkOrderID that the user selects, exists in MasterList, then create a new entry in WOLogSheetNew and copy 'X' fields of data.

What I think I need is:

User makes selection and stores in a collection? then test that data against whatever is in the gallery in question.....so

If the WorkOrderID and TaskDescription that the user has saved to the collection is equal to items in GalleryX and the Status is not equal to Complete, then notify error, else patch collection to SP List...

How does that sound?

[WarrenBelz]

OK @BrendanZ1 ,

I have not changed your code other than the bit I inserted for the gallery and have to rely on you to understand this and put in the correct references - so

If(
   !IsBlank(    
       LookUp(
          WorkOrderListMaster, 
          WorkOrderID=ComboBox2.Selected.WorkOrderID
       )        //If the WorkOrderID in the combobox does not exist in WorkOrderListMaster
   )&&                                                           //AND
   Lookup(
      YourCollectionName,                                        //In your collection 
      WorkOrderID=YourGalleryName.Selected.WorkOrderID &&        //If the WorkOrderID AND
      TaskDescription=YourGalleryName.Selected.TaskDescription,  //The TaskDescription
      Status                                                     //match the gallery item AND
   )="Complete",                                                 //The Status is Complete
   Patch(                                                        //then Patch a new record
      WOLogSheetNew,                                             //to WOLogSheetNew
         Defaults(WOLogSheetNew), 
         {
            WorkOrderID: ComboBox2.Selected.WorkOrderID, 
            Title: VarStockCode.Title, 
            AssignedToEmail:VarStockCode.AssignedToEmail, 
            TaskDescription:Dropdown2.Selected
         }
      )
   ),
   Notify("Record Exists . . . . ")                          //otherwise put up a warning
);
Reset(ComboBox2);
Reset(Dropdown2)

You can delete the comments after the // after you get your mind around this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[BrendanZ1]

@WarrenBelz thanks for the additional explanations, as this really helps with pulling apart the syntax.

One question I have though, is the change at the start, where my code initiates the variable: VarStockCode.

Based on what you have, can I just initiate this at the start like this;

Set(VarStockCode,
LookUp(
          WorkOrderListMaster, 
          WorkOrderID=ComboBox2.Selected.WorkOrderID
       )

I'll have a go at working through your solution today and report back.

Thanks

[WarrenBelz]

@BrendanZ1 ,

That would have to be at the OnVisible of the form to make sure it will not change. You would then test for !IsBlank(varStockCode)

[WarrenBelz]

Hi @BrendanZ1 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[BrendanZ1]

Sorry I haven't had a chance to apply this just yet to test, but will report back ASAP.

[BrendanZ1]

@WarrenBelz  So looking at this now and from what I understand, I would need to place the Set Variable code to the OnVisible of the screen as I am not using a form. If that is correct, my next step is to create a collection in order for this solution to work?

If so, I am trying to by creating a button that submits the choices from the Combobox and Dropdown, to a collection. Then using the Confirm Selection button, this would then perform the work of comparing the data to determine if it can be patched.

If I am still on the right path, then I am having some trouble with getting a collection to work when using both a combobox and dropdown. At the moment I have done the following:

OnVisible for the screen: 

Set(
    VarStockCode,
    LookUp(
        WorkOrderListMaster,
        WorkOrderID = ComboBox2.Selected.WorkOrderID
    )
)

Added a button - Selectbtn, which has the following for the OnSelect property: 

Collect(JobLog, {WorkOrderID: Value(ComboBox2.Selected.WorkOrderID), TaskDescription: Dropdown2.Selected.Value})

My issue is with the Collect function above, as it is not accepting my code for the Combobox......

I am also guessing I would need to ensure that the collection is cleared once the patch function is run?

Sorry for more questions but hopefully I am on the right track

Then my other code should work......

[WarrenBelz]

@BrendanZ1 ,

One thing I noticed is if you are doing this at Screen OnVisible

Set(
    VarStockCode,
    LookUp(
        WorkOrderListMaster,
        WorkOrderID = ComboBox2.Selected.WorkOrderID
    )
)

Then why not use the Variable in the Collect (and why not ClearCollect)? 

ClearCollect(
   JobLog, 
   {
       WorkOrderID: varStockCode,
       TaskDescription: Dropdown2.Selected.Value
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.