Patch a Collection to SharePoint List - All Entries Identical

[tagustin2020]

Hello,

I'm trying to Patch a Collection to a SharePoint list, but all it does is write the same exact entry for whatever number of entries are in the Collection rather than looping through each individual one and recording it back correctly. Here is what the screen looks like before I press the log entries button (note the 2 entries in the lower right quadrant.

When I examine the Items property of the Collection, both entries are showing up.

It gets written back to SharePoint like this. As you can see it only appears to be picking up the first barcode value that was scanned.

Here is the OnSelect formula for the "Log Your Entries" button. I tried it without the "As item" reference as well with the same result.

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': lblBarcode.Text,
            'Operator Email':lblEmail.Text
        }
    )
);
Clear(colTracking); Reset(txtScanBenchBarcode)

Here is the current OnChange property of the Step 2 text input field. The group I am making the app for wants Operators to have immediate feedback as to whether or not the tools match the bench they are working at and whether or not the calibration is past due so in a perfect world, I'd like to be able to grab the information from the "Expected Tools" gallery on the left and patch the values (using the barcode as the key) to my "Admin Scan Log". Is that possible? If so, how do I go about it?

Collect(colTracking,{'Barcode Value':Self.Text,'Operator Email':varUser.Email});Reset(Self);SetFocus(Self);

 The items property of the Your Tools gallery is:

colTracking

Thanks in advance for your help!

Teresa

[SebS]

@tagustin2020 

use lookup if 'Calibration Past Due'  is True make Green Icon visible and when 'Calibration Past Due'  is False make Red Visible

Replace DataSource with name of DataSource where 'Calibration Past Due'  column is located

LookUp(DataSource,'BarCode Value' = Thisitem.'Barcode Value','Calibration Past Due')

[tagustin2020]

@SebS 

Hello Seb,

Thanks for all your help! I was getting a red line under the Not so I wrote the formula like this which seems to be working.

Not(ThisItem.'Barcode Value' in galBenches.AllItems.'Barcode Value')

I could use some help with the Green Check icon that indicates a tool is OK when it comes to calibration.

The formula you gave me for the Red X icon is:

LookUp('Admin Desk Tools', 'Barcode Value'=ThisItem.'Barcode Value','Calibration Past Due')

How would I adjust this to display the icon when the toggle value is false? See reference gallery to the left.

Kind regards,

Teresa

[tagustin2020]

@SebS 

Hello Seb,

Thank you for the "On Select" formula reply. I compared the formula you gave me (see below):

If(IsBlank(Lookup('Admin Desk Tools','Barcode Value' = item.'Barcode Value'),false,true) 

With the formula I had typed in and didn't see a difference, but I went ahead and copy/pasted the above formula over my own just in case. I'm still getting an error. I tried putting an extra parenthesis after the curly brace in case I misunderstood you, but that didn't work either. Can you please advise? Thanks! Teresa

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': item.'Barcode Value',
            'Operator Email':item.'Operator Email',
            'Calibration Past Due':If(IsBlank(Lookup('Admin Desk Tools','Barcode Value' = item.'Barcode Value'),false,true)
        }
    )
);
Clear(colTracking); Reset(txtScanBenchBarcode)

[tagustin2020]

@SebS 

Hello Seb,

Apologies for the scope creep on this thread. I've already submitted a couple of pending questions to you so let me know if I'm lobbing too much over fence as I realize you are busy with your own projects. I do greatly appreciate your help and like how the app is shaping up. As I have been testing the app, I realized that it would be helpful if the Tool Description was included in the Collection to the right as well as having it in the left-hand data source gallery as it makes it easier to figure out what is still missing. Is there a LookUp formula I can plug into the Text property of the label to achieve this?

[SebS]

Hi @tagustin2020

Sorry, I was away from PC for a full day, and I'm just back.  

Just to make sure you understand, LookUp is a fairly easy function, and you can fetch any data from one table and transfer it to another table or label.

In Your scenario, most of the time, the Matching part will be handled by matching 'Barcode Value'

LookUp(DataSource, Matching Condition ('Barcode Value' = ThisItem.'Barcode Value'), Column You would like to pull Information from (Description))

This Item only works inside of the Gallery so if You want to use LookUp outside of the Gallery You need to hard code or You can use Gallery.Selected.'Barcode Value' property for example

LookUp(DataSource, 'Barcode Value' = ThisItem.'Barcode Value', Description)

I hope You sorted your Calibration check and Equipment Check, as they are showing nicely at your gallery now.

If You use Modern Controls, I suggest using Information Control. It's really nice to show any descriptions in a clean way for the User.

[tagustin2020]

@SebS 

Hello Seb,

No worries about the delay. You've been wonderful! I'm not completely done figuring things out, but I've made a lot of progress thanks to you! I provided a demo to the team, and they are very excited about the progress so thanks so much for your help! I was able to bring in the Asset Numbers and Descriptions into the Collection, but I'm having trouble writing the values back to the SharePoint list. Only the first value in the list, in this example, "Notepad" is written back to the SharePoint list. 

Here is the latest version of the "Submit Scans" OnSelect formula. There are no errors, but only the first item writes back to the list. I tried inserting "item." in front of the lbl references, but it shows an error. Something about it not expecting Text.

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': item.'Barcode Value',
            'Operator Email': item.'Operator Email',
            'Tool Description': lblDescriptionG.Text,
            'Tool Asset Number': lblToolAssetG.Text,
            Mismatch: tglMismatch.Value,
            'Calibration Past Due':tglPastDue.Value
        }
    )
);
Clear(colTracking);
Reset(txtScanBenchBarcode);
SetFocus(txtScanBenchBarcode);
Navigate('Confirmation Screen');

I removed the following formula from the OnSelect because it shows as an error, but I still need to figure out a way to work it in.

'Calibration Past Due':If(IsBlank(Lookup('Admin Desk Tools','Barcode Value' = item.'Barcode Value'),false,true)

I did ping you several times today on this thread with different questions so if you wouldn't mind checking them out, I'd be grateful. 

Kind regards,

Teresa

[SebS]

@tagustin2020 

Calibration missing just one ")" it should work now

'Calibration Past Due':If(IsBlank(Lookup('Admin Desk Tools','Barcode Value' = item.'Barcode Value')),false,true)

The ForAll is simple. You never refer to control. You need to refer to the item in the collection I assumed both columns names are the same in collection and sharepoint

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': item.'Barcode Value',
            'Operator Email': item.'Operator Email',
            'Tool Description': item.'Tool Description',
            'Tool Asset Number': item.'Tool Asset Number',
            Mismatch: tglMismatch.Value,
            'Calibration Past Due':tglPastDue.Value
        }
    )
);
Clear(colTracking);
Reset(txtScanBenchBarcode);
SetFocus(txtScanBenchBarcode);
Navigate('Confirmation Screen');

[tagustin2020]

@SebS 

Thank you for the quick response. This part of the formula is working.

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': item.'Barcode Value',
            'Operator Email': item.'Operator Email',
            Mismatch: tglMismatch.Value,
            'Calibration Past Due': tglPastDue.Value
        }
    )
);
Clear(colTracking);
Reset(txtScanBenchBarcode);
SetFocus(txtScanBenchBarcode);
Navigate('Confirmation Screen');

I had tried the item approach given your previous advice, but wasn't able to get it to work. These are the 2 problem formulas.

'Tool Description': item.'Tool Description',
            'Tool Asset Number': item.'Tool Asset Number',

When I hover over them, I get the following error message.

When I type item. it only gives me these 2 options to choose from.

Thanks for hanging in there with me. What should I try next?

Teresa

[SebS · Accepted solution]

@tagustin2020 

My bad 😛 I forgot that this collection only stores barcodes and emails ;]

ForAll(
    colTracking As item,
    Patch(
        'Admin Scan Log',
        Defaults('Admin Scan Log'),
        {
            'Barcode Value': item.'Barcode Value',
            'Operator Email': item.'Operator Email',
            'Tool Description': Lookup('Admin Desk Tools','Barcode Value' = item.'Barcode Value','Tool Description'),
            'Tool Asset Number': Lookup('Admin Desk Tools','Barcode Value' =          item.'Barcode Value','Tool Asset Number'),
            Mismatch: tglMismatch.Value,
            'Calibration Past Due':Lookup('Admin Desk Tools','Barcode Value' =          item.'Barcode Value','Past Due?')
        }
    )
);
Clear(colTracking);
Reset(txtScanBenchBarcode);
SetFocus(txtScanBenchBarcode);
Navigate('Confirmation Screen');

 Now your job is to correct all Names of Columns in LookUps to match names in 'Admin Desk Tools'  table

[tagustin2020]

@SebS 

Hi Seb,

Thank you for providing this formula. I have to run to a company dinner, so I'll have to take a closer look over the weekend. Have a great one!

Teresa