Collect and patch Lookup, image, Date and Name sharepoint columns

[Anonymous]

Howdy all,

I have an app thats works online and offline if the user is unable to get internet connection, what is the syntax to

(**these are not in a form)

collect a lookup column on the canvas? (One is a name the other is a choice column) 

- Do i create a custom column and add every single user that they may select? ie ["bob.bobson@company.com", "chewy.bakka@company.com"] Etc

collect a date column on the canvas? is this a text input label?

collect an image taken from a camera on the canvas

i understand how to patch them using this 
Activity:{Value:dcvactivity.Selected.Value,'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}

do i still use this in the collect(Activity:{Value:dcvactivity.Selected.Value,'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}, ETC ETC

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about your issue?

Could you please show more details about the Activity column in your SP list? Is it a Choice type column?

Further, could you please share a bit more about the LookUp column? Do you enable "Allow multiple selections" option for it?

If you want to collect LookUp type column into a Collection, please take a try with the following formula:

Collect(
            LocalCollection, 
            {
             ...
             LookUpColumn: {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp(ReferencedSPList, ReferencedColumn = LookUpColumnDropdown.Selected.Value),
                                  Value: LookUpColumnDropdown.Selected.Value 
                           },
             ...
            }
)

Note: The ReferencedSPList represents the SP list your LookUp column references values from. The ReferencedColumn represents the column (your LookUp column references values from) in your ReferencedSPList.

I suppose that the Activity column is a Choice type column in your SP list, is it true? If you want to collect the Activity column value into your Collection, actually, it is not necessary to use the formula that you provided, you could consider save the Activity column value (selected Dropdown value) into your Collection directly as below:

Collect(
           LocalCollection,
           {
              ...
             Activity: dcvactivity.Selected.Value,  /* <-- Directly save the selected Dropdown value into your collection */
             ...
           }
)

When you want to patch above collection (your local collection) into your SP list, you need to type the following formula:

ForAll(
          LocalCollection,
          Patch(
                       'YourSPList',
                        Defaults('YourSPList'),
                         {
                                 ...
                                  Activity: {
                                              '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                                               Value: LocalCollection[@Activity]       
                                  },
                                  ...
                        }
           )
)

More details about the ForAll function and Patch function, please check the following article:

ForAll function

Patch function

Best regards,

[Anonymous]

The lookup column im trying to reference is called 'Mine Call' it is stored in another list called 'Asset List' for powerbi to function i need it to be the column i reference (hope that makes sense), it doesnt have allow multiple selections and is a single line of text in the sharepoint database im referencing, (in the 'Haulage Operators Log' its a choice column)

As per your recommendation on my on onselect button i now have the following syntax 

ForAll(RD18COLLECTION,
Patch('Haulage Operators Log',
Defaults('Haulage Operators Log'),
{
to: Can_to.Selected,
From: Can_From.Selected,
Pit: Can_Pit.Selected,
Activity:Can_Activity.Selected,
starthours: Can_Starthours.Text,
finishhours: Can_Finishhours.Text,
'Asset ID': { '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Value: RD18COLLECTION[@'Asset ID'] },
}
)
);Navigate(DetailScreen1, ScreenTransition.None)

& 

On my timer end (to submit the data if there is internet connection) i now have

If(Connection.Connected,
ForAll(RD18COLLECTION,
Patch('Haulage Operators Log',
Defaults('Haulage Operators Log'),
{
to: to,
From: From,
Pit: Pit,
Activity: Activity,
'Asset ID': 'Asset ID',
starthours: starthours,
finishhours: finishhours

}

)

);
Clear(RD18COLLECTION)
)

I get the following two errors, in the onselect 

Invalid argument type. Expecting a Record value, but of a different schema.

and on timer end i get

The type of this argument 'Asset_x0020_ID' does not match the expected type 'Record'. Found type 'Error'. Location

[v-xida-msft]

Hi @Anonymous ,

Could you please share a bit more about the error message with your formula?

How do you save records into your local collection?

Could you please share more details about the data structure of your RD18COLLECTION? Could you please share me a screenshot?

Further, could you please share a bit more about the 'Asset ID' column in your SP list? Is it a Choice type column?

Just an update -- If you want to collect LookUp type column into a Collection, please take a try with the following formula:

Collect(
            LocalCollection, 
            {
             ...
             LookUpColumn: {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp(ReferencedSPList, ReferencedColumn = LookUpColumnDropdown.Selected.Value, ID), /* <--  Modify formula here */
                                  Value: LookUpColumnDropdown.Selected.Value 
                           },
             ...
            }
)

Or

Collect(
            LocalCollection, 
            {
             ...
             LookUpColumn: {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp(ReferencedSPList, ReferencedColumn = LookUpColumnDropdown.Selected.Value).ID, /* <-- Modify formula here */
                                  Value: LookUpColumnDropdown.Selected.Value 
                           },
             ...
            }
)

Please modify the formula within the OnTimerEnd property of your Timer control to following:

If(
   Connection.Connected,
   ForAll(
           RD18COLLECTION,
           Patch(
                 'Haulage Operators Log',
                  Defaults('Haulage Operators Log'),
                  {
                    to: RD18COLLECTION[@to],
                    From: RD18COLLECTION[@From],
                    Pit: RD18COLLECTION[@Pit],
                    Activity: RD18COLLECTION[@Activity],
                   'Asset ID': RD18COLLECTION[@'Asset ID'],
                    starthours: RD18COLLECTION[@starthours],
                    finishhours: RD18COLLECTION[@finishhours]
                   }
               )
          );
          Clear(RD18COLLECTION)
)

Please share more details about the error message within the OnSelect property of the Button control. The error message that you provided seems to tell that there is something wrong with the values you provided for the arguments within the Patch function.

Please check if you have provided a proper values for the arguments within the Patch function.

Best regards,

[Anonymous]

---

@v-xida-msft wrote:

Hi @Anonymous ,

Could you please share a bit more about the error message with your formula?

How do you save records into your local collection?

Could you please share more details about the data structure of your RD18COLLECTION? Could you please share me a screenshot?

Further, could you please share a bit more about the 'Asset ID' column in your SP list? Is it a Choice type column?

Just an update -- If you want to collect LookUp type column into a Collection, please take a try with the following formula:

Collect(
            LocalCollection, 
            {
             ...
             LookUpColumn: {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp(ReferencedSPList, ReferencedColumn = LookUpColumnDropdown.Selected.Value, ID), /* <--  Modify formula here */
                                  Value: LookUpColumnDropdown.Selected.Value 
                           },
             ...
            }
)

Or

Collect(
            LocalCollection, 
            {
             ...
             LookUpColumn: {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp(ReferencedSPList, ReferencedColumn = LookUpColumnDropdown.Selected.Value).ID, /* <-- Modify formula here */
                                  Value: LookUpColumnDropdown.Selected.Value 
                           },
             ...
            }
)

Please modify the formula within the OnTimerEnd property of your Timer control to following:

If(
   Connection.Connected,
   ForAll(
           RD18COLLECTION,
           Patch(
                 'Haulage Operators Log',
                  Defaults('Haulage Operators Log'),
                  {
                    to: RD18COLLECTION[@to],
                    From: RD18COLLECTION[@From],
                    Pit: RD18COLLECTION[@Pit],
                    Activity: RD18COLLECTION[@Activity],
                   'Asset ID': RD18COLLECTION[@'Asset ID'],
                    starthours: RD18COLLECTION[@starthours],
                    finishhours: RD18COLLECTION[@finishhours]
                   }
               )
          );
          Clear(RD18COLLECTION)
)

Please share more details about the error message within the OnSelect property of the Button control. The error message that you provided seems to tell that there is something wrong with the values you provided for the arguments within the Patch function.

Please check if you have provided a proper values for the arguments within the Patch function.

Best regards,

---

Hi, Firstly thankyou for you time,

Currently there is no error messages, (desperately trying to figure it out) , I patch the records straight into sharepoint if there is internet connection if not it collects the data into the local collection (RD18COLLECTION) using the following code on a button with OnSelect,

If(Connection.Connected
,Patch('Haulage Operators Log',
Defaults('Haulage Operators Log'),
{
to: Can_to.Selected,
From: Can_From.Selected,
Pit: Can_Pit.Selected,
Activity:Can_Activity.Selected,
starthours: Can_Starthours.Text,
finishhours: Can_Finishhours.Text,
'Asset ID' : {Id: Can_AssetID.Selected.Id, Value: Can_AssetID.Selected.Value, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}
}
)
,Collect(RD18COLLECTION,
{
to: Can_to.Selected,
From: Can_From.Selected,
Pit: Can_Pit.Selected,
Activity:Can_Activity.Selected,
'Asset ID': Can_AssetID.Selected,
starthours: Can_Starthours.Text,
finishhours: Can_Finishhours.Text
}
)
);
UpdateContext({ResetVar: true});
UpdateContext({ResetVar:false});
Navigate(DetailScreen1, ScreenTransition.None)

and if their is no connection and it collects there is a timer using the OnTimerEnd with the following code,

If(
Connection.Connected,
ForAll(
RD18COLLECTION,
Patch(
'Haulage Operators Log',
Defaults('Haulage Operators Log'),
{
to: RD18COLLECTION[@to],
From: RD18COLLECTION[@From],
Pit: RD18COLLECTION[@Pit],
Activity: RD18COLLECTION[@Activity],
starthours: RD18COLLECTION[@starthours],
finishhours: RD18COLLECTION[@finishhours],
'Asset ID': RD18COLLECTION[@'Asset ID']
}
)
);
Clear(RD18COLLECTION)
)

The Lookup column is located here https://ozmining.sharepoint.com/sites/Pajingo/Production/Lists/Haulage%20Operators%20Log/ and is called Asset ID (Its a choice column) the Location of the lookup column is located here https://ozmining.sharepoint.com/sites/Pajingo/Production/Lists/Assets%20List/AllItems.aspx and it is a Text column (Single line)

the Data structure of RD18COLLECTION is below (i have no idea why it has two of every column>?)

and yes Asset ID is a choice column in the database im populating, but a text column in the referenced list

And i should add im currently modifying the choice values to ID as follows (attempting to get it too work)

Table({Id: 1, Value: "RD18"}, {Id: 2, Value: "RD19"}, {Id: 3, Value: "RD20"},{Id: 4, Value: "RD21"},{Id: 5, Value: "RD33"})

[Anonymous]

'Asset ID': {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp('Assets List', 'Mine Call' = Can_AssetID.Selected.Value, ID),
                                  Value: Can_AssetID.Selected.Value 

&

'Asset ID': {
                                 '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"           
                                  Id: LookUp('Assets List', 'Mine Call' = Can_AssetID.Selected.Value).ID,
                                  Value: Can_AssetID.Selected.Value

Both give the same error

The function collect has some invalid arguements. 

[Anonymous · Accepted solution]

For future people this is how i solved it

my OnSelect is

If(Connection.Connected
	,Patch('Haulage Operators Log',
		Defaults('Haulage Operators Log'),
		{
		to: Can_to.Selected,
		From: Can_From.Selected,
		Pit: Can_Pit.Selected,
		Activity:Can_Activity.Selected,
		starthours: Can_Starthours.Text,
		finishhours: Can_Finishhours.Text,
        Assetid: {Id:Can_Assetid.Selected.Id,Value:Can_Assetid.Selected.Id, '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}
		}
	)
	,Collect(RD18COLLECTION,
		{
		to: Can_to.Selected,
		From: Can_From.Selected,
		Pit: Can_Pit.Selected,
		Activity:Can_Activity.Selected,
		starthours: Can_Starthours.Text,
		finishhours: Can_Finishhours.Text,
        Assetid: {Id:Can_Assetid.Selected.Id,Value:Can_Assetid.Selected.Id, '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"}
		}
	)
);
UpdateContext({ResetVar: true}); 
UpdateContext({ResetVar:false});
Navigate(DetailScreen1, ScreenTransition.None)

and the choice column was changed to an Id column using the ID column in the lookup lists 

Table({Id: 25, Value: "RD20"},{Id: 26, Value: "RD21"},{Id: 27, Value: "RD18"},{Id: 28, Value: "RD33"},{Id: 52, Value: "RD19"})

It works now