Calling on a specific item from a Collection

[JeckGill]

I want to populate a table with specific data from an app - is there a way I can tell a patch to take a specific entry on a collection. For example:

I want to use the "2" from "Rest Room/Canteen" in the following expression:

ForAll(col_actionRequired,
    Patch(
        'Form - Site Inspection Reports',
        Defaults('Form - Site Inspection Reports'),
        {
            'Primary Key': _formName,
            Name: _uploaderName,
            'Project Number': _projectNumber,
            Signature: signatureOutput.Image,
            'Problem Area': problemArea,
            'Action Required': actionRequired,
            'Have the changes been made?': "No",
            'Rest Room / Canteen': col_combined.XYZ
            
        }            
    )
);

is this possible?

[VauBee]

Hi @JeckGill 

'Rating Score': ThisRecord.RatingScore, 

or 

'Rating Score': col_combined[@RatingScore], 

should work.

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[JeckGill]

so for the last line in that expression I have

'Rest Room / Canteen': col_combined.XYZ //where col_combined.XYZ would be the collection entry marked as "2"

I don't want 'Rating Score' to be put through, just the score itself  

[timl]

@JeckGill 

>>I don't want 'Rating Score' to be put through, just the score itself

With regards to this, where do you store the 'score itself'? Is this a column in your col_actionRequired collection? If so, can you post the contents of this collection?

[JeckGill]

the collection is quite large - so I wouldn't be able to post all entries; hence why I only put a snippet of them earlier. Here are more of the entries just so you have an idea.

There are 49 items, they get rated either N/A or 1-5. All of them need to be uploaded and they have their own column that they correlate with in the PowerApps table.

my plan was to just take the rating value from the collection based on it's location (they should always be created in the same order, I can't see why they wouldn't so it was the best way I could think of doing this), and patch that through to the table

[timl]

@JeckGill 

Thanks for the explaination. Just to clarify my understanding, you'd like to Lookup the value of RatingScore in col_combined, where the RatingTitle equals "Rest Room/Canteen" (ie, 2) and to patch this to 'Form - Site Inspection Reports'?

If so, the syntax would look like this:

ForAll(col_actionRequired,
    Patch(
        'Form - Site Inspection Reports',
        Defaults('Form - Site Inspection Reports'),
        {
            'Primary Key': _formName,
            Name: _uploaderName,
            'Project Number': _projectNumber,
            Signature: signatureOutput.Image,
            'Problem Area': problemArea,
            'Action Required': actionRequired,
            'Have the changes been made?': "No",
            'Rest Room / Canteen': LookUp(col_combined, RatingTitle="Rest Room / Canteen").RatingScore
            
        }            
    )
);

[VauBee]

Hi @JeckGill 

'Rating Score': ThisRecord.RatingScore,  or  'Rating Score': col_combined[@RatingScore],  

would patch the score value, e.g. 2, to a datasource column named "Rating score". Chose this bc no column name was provided 😉

You can patch any column like so.

Do I get it right, that every column name corresponds to the records RatingTitle in your collection?

If so, this might work:

ThisRecord.RatingTilte: ThisRecord.RatingScore,

------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up. 

[JeckGill]

I've been trying @timl's method - I have it almost working I think. But I have red lines under the whole patch now 😅

I get two messages when I hover the cursor over these;
- "The type of this argument 'gg_pub'accessegress' does not match the expected type 'OptionSetValue (Site Inspection Rating)'. Found type 'Text'.
- "The function 'Patch' has some invalid arguments.

Below is the current expression for reference:

ForAll(col_actionRequired,
    Patch(
        'Form - Site Inspection Reports',
        Defaults('Form - Site Inspection Reports'),
        {
            'Primary Key': _formName,
            Name: _uploaderName,
            'Project Number': _projectNumber,
            Signature: signatureOutput.Image,
            'Problem Area': problemArea,
            'Action Required': actionRequired,
            'Have the changes been made?': "No",

            'Rest Room / Canteen': LookUp(col_combined, RatingTitle="Rest Room / Canteen").RatingScore,
            'Changing Facilities': LookUp(col_combined, RatingTitle="Changing Facilities").RatingScore,
            'General Cleanliness': LookUp(col_combined, RatingTitle="General Cleanliness").RatingScore,
            'Site offices and welfare electrical cert in date': LookUp(col_combined, RatingTitle="Site offices & welfare electrical cert in date").RatingScore,
            'First aid facilities / first aider': LookUp(col_combined, RatingTitle="First Aid Facilities/ First Aider").RatingScore,
            Washing: LookUp(col_combined, RatingTitle="Washing").RatingScore,
            Sanitation: LookUp(col_combined, RatingTitle="Sanitation").RatingScore,
            PPE: LookUp(col_combined, RatingTitle="PPE").RatingScore,
            'Fire extinguisher checked & in date - welfare': LookUp(col_combined, RatingTitle="Fire extinguisher in date  & checked").RatingScore,
            'Environmental Factors': LookUp(col_combined, RatingTitle="Environmental Factors").RatingScore,
            'Accident Records': LookUp(col_combined, RatingTitle="Accident Records").RatingScore,
            'Statutory forms / registers etc. ': LookUp(col_combined, RatingTitle="Statutory forms / Registers etc.").RatingScore,
            'Access / Egress': LookUp(col_combined, RatingTitle="Access / Egress").RatingScore,
            'Signs & Notices': LookUp(col_combined, RatingTitle="Signs & Notices").RatingScore,
            'Tidiness - Storage Areas': LookUp(col_combined, RatingTitle="Tidiness").RatingScore,
            Flammables: LookUp(col_combined, RatingTitle="Flammables").RatingScore,
            Gases: LookUp(col_combined, RatingTitle="Gases").RatingScore,
            'Fuels & Lubricants': LookUp(col_combined, RatingTitle="Fuels / Lubricants").RatingScore,
            'Fire precautions - Storage Areas': LookUp(col_combined, RatingTitle="Fire Precautions").RatingScore,
            'Tools & Equipment': LookUp(col_combined, RatingTitle="Tools & Equipment").RatingScore,
            'Spillage kits': LookUp(col_combined, RatingTitle="Spillage Kits").RatingScore,
            'Access Equipment': LookUp(col_combined, RatingTitle="Access Equipment").RatingScore,
            'Dangerous Materials (COSHH)': LookUp(col_combined, RatingTitle="Dangerous Materials (COSHH)").RatingScore,
            'PAT Testing in date': LookUp(col_combined, RatingTitle="PAT Testing in date").RatingScore,
            'Quarantine Area': LookUp(col_combined, RatingTitle="Quarantine Area in place & used").RatingScore,
            'COSHH - SDS assessments': LookUp(col_combined, RatingTitle="COSHH - SDS Assessments in place").RatingScore,
            'LOLER in date': LookUp(col_combined, RatingTitle="LOLER in date").RatingScore,
            Scaffolding: LookUp(col_combined, RatingTitle="Scaffolding").RatingScore,
            'Ladders & Trestles': LookUp(col_combined, RatingTitle="Ladders / Trestles").RatingScore,
            'Working Platforms': LookUp(col_combined, RatingTitle="Working Platforms").RatingScore,
            Services: LookUp(col_combined, RatingTitle="Services").RatingScore,
            Security: LookUp(col_combined, RatingTitle="Security").RatingScore,
            'Extinguisher checked + in date - ConsAct': LookUp(col_combined, RatingTitle="Extinguisher in date").RatingScore,
            'Call points checked': LookUp(col_combined, RatingTitle="Call points checked").RatingScore,
            Electrical: LookUp(col_combined, RatingTitle="Electrical").RatingScore,
            Hoists: LookUp(col_combined, RatingTitle="Hoists").RatingScore,
            'Mobile Crane': LookUp(col_combined, RatingTitle="Mobile Crane").RatingScore,
            'Lifting Gear': LookUp(col_combined, RatingTitle="Lifting Gear").RatingScore,
            Excavations: LookUp(col_combined, RatingTitle="Excavations").RatingScore,
            Transport: LookUp(col_combined, RatingTitle="Transport").RatingScore,
            'Other Plant': LookUp(col_combined, RatingTitle="Other Plant").RatingScore,
            Machinery: LookUp(col_combined, RatingTitle="Machinery").RatingScore,
            'Power Tools': LookUp(col_combined, RatingTitle="Power Tools").RatingScore,
            'Hand Tools': LookUp(col_combined, RatingTitle="Hand Tools").RatingScore,
            'Tidiness - Construction Activities': LookUp(col_combined, RatingTitle="Tidiness").RatingScore,
            'Noise Levels': LookUp(col_combined, RatingTitle="Noise Levels").RatingScore,
            'Traffic Management': LookUp(col_combined, RatingTitle="Traffic Management").RatingScore,
            'Restricted Area': LookUp(col_combined, RatingTitle="Restricted Area").RatingScore
        }
    )
);

EDIT 1:
Now I'm just even more confused - I ran a quick check just to double check it was working. No issue:

Is there any reason it should be giving me a problem I'm not aware of?

EDIT 2:
Text(LookUp(col_combined, RatingTitle="Access / Egress").RatingScore) generates the exact same as the image in Edit 1 -  but makes no difference to the error this comment initially talks about 

@VauBee Have you seen something similar before?

[VauBee]

@JeckGill 

not 100% sure.. is "N/A" part of your Choice(s)-columns OptionSet-Values? Error says: Hey, I expect an OptionSetValue, but you're a giving me text... did you try to append .Value  or transform it to Value()?

I'm still curious, if  

ThisRecord.RatingTilte: ThisRecord.RatingScore,

would work 😉

[JeckGill]

absolutely positive that N/A is an option, however it's numerically assigned to 0

just to make sure I change the value selected and ran through it again on the check sheet

wrapping it in Text() and Value () both get this

Appending .Text and .Value changes it to an error

I'm unsure how to use your 

'Rating Score': ThisRecord.RatingScore, 

lines would work - the collection that the ForAll is working from is different to the one where the ratings are stored so I don't think "ThisRecord" would work. The ForAll uses all in col_actionRequired; all these ratings are being taken specifically from col_combined. I have tried your "'Rating Score': col_combined[@RatingScore]" line but that didn't do much; which lead me down the LookUp path. I feel there is something obvious I'm missing but I've been staring at this problem for so long now I can't make heads or tails of it 😂

I'll append code again at the end of this message- but here's the gist of what I am trying to do just incase it helps you understand my problem a bit more 😅

col_actionRequired stores the name of a problem that received a rating of 1 or 2, as well as whatever the user has written in the app which would serve as a means to mitigate the issues. Each row will repost the same data except for the col_actionRequired parts; which means I have added a toggle just to tick off when the changes have been made.

In the end, my PowerApps table will look (very very abstractly) like this:

PrimaryKey	Problem title	Problem Fix	Ratings 1-49
key1	PPE	blah blah blah	x x x x x x x
key1	Access/Egress	blah blah blah	x x x x x x x
key2	First Aid	blah blah blah	x x x x x x x
key2	Fire Precautions	blah blah blah	x x x x x x x
key2	PPE	blah blah blah	x x x x x x x
key3	Tidiness	blah blah blah	x x x x x x x

Here's the code again; if you have any more questions please do ask as I am at my wits end with this and I am so close to using my laptop as a sled to go down the stairs 😅

ForAll(col_actionRequired,
    Patch(
        'Form - Site Inspection Reports',
        Defaults('Form - Site Inspection Reports'),
        {
            'Primary Key': _formName,
            Name: _uploaderName,
            'Project Number': _projectNumber,
            Signature: signatureOutput.Image,
            'Problem Area': problemArea,
            'Action Required': actionRequired,
            'Have the changes been made?': "No",

            'Rest Room / Canteen': LookUp(col_combined, RatingTitle="Rest Room / Canteen").RatingScore,
            'Changing Facilities': LookUp(col_combined, RatingTitle="Changing Facilities").RatingScore,
            'General Cleanliness': LookUp(col_combined, RatingTitle="General Cleanliness").RatingScore,
            'Site offices and welfare electrical cert in date': LookUp(col_combined, RatingTitle="Site offices & welfare electrical cert in date").RatingScore,
            'First aid facilities / first aider': LookUp(col_combined, RatingTitle="First Aid Facilities/ First Aider").RatingScore,
            Washing: LookUp(col_combined, RatingTitle="Washing").RatingScore,
            Sanitation: LookUp(col_combined, RatingTitle="Sanitation").RatingScore,
            PPE: LookUp(col_combined, RatingTitle="PPE").RatingScore,
            'Fire extinguisher checked & in date - welfare': LookUp(col_combined, RatingTitle="Fire extinguisher in date  & checked").RatingScore,
            'Environmental Factors': LookUp(col_combined, RatingTitle="Environmental Factors").RatingScore,
            'Accident Records': LookUp(col_combined, RatingTitle="Accident Records").RatingScore,
            'Statutory forms / registers etc. ': LookUp(col_combined, RatingTitle="Statutory forms / Registers etc.").RatingScore,
            'Access / Egress': LookUp(col_combined, RatingTitle="Access / Egress").RatingScore.Value,       //line with the current issue 
            'Signs & Notices': LookUp(col_combined, RatingTitle="Signs & Notices").RatingScore,
            'Tidiness - Storage Areas': LookUp(col_combined, RatingTitle="Tidiness").RatingScore,
            Flammables: LookUp(col_combined, RatingTitle="Flammables").RatingScore,
            Gases: LookUp(col_combined, RatingTitle="Gases").RatingScore,
            'Fuels & Lubricants': LookUp(col_combined, RatingTitle="Fuels / Lubricants").RatingScore,
            'Fire precautions - Storage Areas': LookUp(col_combined, RatingTitle="Fire Precautions").RatingScore,
            'Tools & Equipment': LookUp(col_combined, RatingTitle="Tools & Equipment").RatingScore,
            'Spillage kits': LookUp(col_combined, RatingTitle="Spillage Kits").RatingScore,
            'Access Equipment': LookUp(col_combined, RatingTitle="Access Equipment").RatingScore,
            'Dangerous Materials (COSHH)': LookUp(col_combined, RatingTitle="Dangerous Materials (COSHH)").RatingScore,
            'PAT Testing in date': LookUp(col_combined, RatingTitle="PAT Testing in date").RatingScore,
            'Quarantine Area': LookUp(col_combined, RatingTitle="Quarantine Area in place & used").RatingScore,
            'COSHH - SDS assessments': LookUp(col_combined, RatingTitle="COSHH - SDS Assessments in place").RatingScore,
            'LOLER in date': LookUp(col_combined, RatingTitle="LOLER in date").RatingScore,
            Scaffolding: LookUp(col_combined, RatingTitle="Scaffolding").RatingScore,
            'Ladders & Trestles': LookUp(col_combined, RatingTitle="Ladders / Trestles").RatingScore,
            'Working Platforms': LookUp(col_combined, RatingTitle="Working Platforms").RatingScore,
            Services: LookUp(col_combined, RatingTitle="Services").RatingScore,
            Security: LookUp(col_combined, RatingTitle="Security").RatingScore,
            'Extinguisher checked + in date - ConsAct': LookUp(col_combined, RatingTitle="Extinguisher in date").RatingScore,
            'Call points checked': LookUp(col_combined, RatingTitle="Call points checked").RatingScore,
            Electrical: LookUp(col_combined, RatingTitle="Electrical").RatingScore,
            Hoists: LookUp(col_combined, RatingTitle="Hoists").RatingScore,
            'Mobile Crane': LookUp(col_combined, RatingTitle="Mobile Crane").RatingScore,
            'Lifting Gear': LookUp(col_combined, RatingTitle="Lifting Gear").RatingScore,
            Excavations: LookUp(col_combined, RatingTitle="Excavations").RatingScore,
            Transport: LookUp(col_combined, RatingTitle="Transport").RatingScore,
            'Other Plant': LookUp(col_combined, RatingTitle="Other Plant").RatingScore,
            Machinery: LookUp(col_combined, RatingTitle="Machinery").RatingScore,
            'Power Tools': LookUp(col_combined, RatingTitle="Power Tools").RatingScore,
            'Hand Tools': LookUp(col_combined, RatingTitle="Hand Tools").RatingScore,
            'Tidiness - Construction Activities': LookUp(col_combined, RatingTitle="Tidiness").RatingScore,
            'Noise Levels': LookUp(col_combined, RatingTitle="Noise Levels").RatingScore,
            'Traffic Management': LookUp(col_combined, RatingTitle="Traffic Management").RatingScore,
            'Restricted Area': LookUp(col_combined, RatingTitle="Restricted Area").RatingScore
        }
    )
);