Problems with Lookup Columns when copying item metadata from one library to another

DURING 2024, MICROSOFT HAS BEEN DEPRECATING FUNCTIONS IN CLASSIC SHAREPOINT. Needless to say this has been causing me problems with the legacy Teamsites with our business relies upon.

The first time I noticed this was in July 2024 when Microsoft turned off our ability to edit the landing pages of Classic Teamsites. I know there are workarounds where the Tenant Admin can fiddle with the SharePoint security settings, but I don't have that level of access and, more importantly, my Teamsite Owners don't.

The next change was in November 2024 when Microsoft turned off the Content Editor and Script Editor web parts, meaning that even if we could edit the landing page, we still wouldn't be able to change or add text, or embed any Power Platform functions on the page. This was real problem for me because one of my clients had asked that I revise a PowerApps Gallery to include additional Archive Libraries. I was able to do the PowerApps part of the work (more on that in a future post) but then wasn't able to embed the new Gallery on the page ... so that was very nearly a wasted effort.

I understand that Changes Happen and that while we're hosting Classic and Modern in the same site farm, there will have to be compromises in the functioning of Classic sites ... but I'm still left with the challenge of circumventing these issues.

There has to be an easier way of transferring data from one list or library to another, right?

It all came down to one not-so-simple solution - I'd have to migrate the functions from Classic to Modern SharePoint.

It's not difficult to make copies of PowerApps and it's not hard to clone Lists and Libraries from Classic to Modern in the same Tenant. The real challenge is to find a way to copy the data from those lists and libraries to the cloned Data Sources.

There are PowerShell methods and some third party tools that can be used to copy data from one place to another in SharePoint but again, that requires Tenant Admin access and that wasn't available to me. A brief conversation with the actual Tenant Admin ended up in a "Dev-UAT-Live, months of work, yadda-yadda" exchange. I haven't used Dev-UAT-Live methodology since the good old days of Visual Interdev/ASP/.NET. All I wanted to do was migrate some data, preferably keeping the Created/Modified dates, but it wouldn't be the end of the world if that data was lost in translation.

It was going to have to be a Power Automate Flow.

Only one of my business solutions uses Document Libraries. Figuring this would be the most difficult, this is where I decided to start.

The several libraries in this solution all had an identical structure (some are live, some are archives) - over 50 columns, some of which are lookups. Even so, I didn't think this would pose much of a problem, as the migration should be possible using just a few steps on Power Automate.

This was my first try:

Not shown here, but you can expand the Get file properties' "Show advanced options" and limit the retrieved items to just one View of the library. This is what I did for testing purposes. Click on the image to enlarge.

... and it almost worked. For some reason the values for one of the Lookup columns, "Class", wasn't migrating across. Which was puzzling me, because the value for "LOB", also a Lookup column, was ported across just fine.

As you can see, the column for Class remains blank.

So, thinking that I must have done something wrong, I tried adding an Update file properties function, which I thought might force the Class column to update. But that didn't work either.

There is a clue here, but I just hadn't spotted it yet.

A bit of Googling around "Lookup column data not migrated by Flow" didn't get me anywhere, either ... and finally, I posted a question on the Microsoft Power Automate community site. One helpful person suggested that I remake the Lists that supply the main library's lookup columns. I had already tried that, but the comment did get me to thinking ...

Lookup columns don't use the Value from the feeder list ... they use the ID. So I took another look at the source feeder list and the destination feeder list. And this is what I saw ...

The IDs of the corresponding Lookup values were different. So, during the migration process, the Flow was looking for a Class Value with the ID of 63 in the destination Lookup column and not finding it.

It looked to me like the original builder of the Libraries had created the Lookup list for Class, then added and deleted the values a few times, ending up with a list of values where the lowest ID was 63. When I had cloned the Library across to the destination Modern site, I had added the values for the Class Lookup column and naturally the ID numbers started with 1. Deleting the Lookup list and starting again just repeated the problem.

The solution was to first add (and delete) 62 items to the Lookup list, then add the correct values, slowly, one-at-a-time, to get the ID numbers in the two Lookup lists to correspond. Once I'd done that I could just run the Flow (as pictured above) and everything worked fine ...

This time the Flow worked perfectly, because the IDs in the source and destination Lookup lists matched.

SAVING THE CREATED AND MODIFIED DATES

I did fret a little about this. While it's not possible, at least with a Flow, to replicate the Created and Modified dates from one List/Library to another, it did occur to me that I could add two new date columns to the destination Library - "Legacy_Created" and "Legacy_Modified" - and use the Flow's Update file properties function to copy the dates from the source Created column to the destination's Legacy_Created column.

I hope that helps someone.

Next: Moving List items from a live list to an archive list

PowerApps - Retrieve an Employee's ID (employeeId)

YOU'D THINK IT'D BE EASIER TO GET THE EMPLOYEE ID, RIGHT? Just add Office365 as a data source to your Power Apps form then call:

Office365Users.UserProfile(ThisItem.Id).employeeId

... or something similar. But no. Can't do it that way.

As is my habit with this sort of thing, I went first to Google. And from what I could see there wasn't a way to get the employeeId natively in PowerApps. Any solution I found wanted me to run a PowerAutomate Flow, which wasn't what I was after.

Then I had a breakthrough. I hadn't realised it before, but there is also a flavour of Power Automate you can call directly from a Power App - it really is a constant learning journey, this PowerApps stuff, isn't it?

WHAT I NEEDED IT FOR

There are probably many scenarios where you might need to know a colleague's employeeId. By that I mean the multi-digit label (often sourced from WorkDay) that Active Directory copies and uses to identify a colleague. In the case of our corporation, it's a six digit number that you can see in Delve but can't otherwise access, at least not programmatically.

Why does Microsoft display the Employee ID in Delve, yet won't let you retrieve it via the Office365Users method?

In this case, I was looking to allow colleagues to book a session with the official corporate photographer. In order to differentiate between two John Smiths, we name the images in the format:

FirstName_Surname_EmployeeID

Then they all get stored in a SharePoint Document library and I can showcase them to the Business using a SharePoint Gallery (out of scope for this blog post).

HOW I DID IT

The first part is fairly easy. I created a SharePoint list with a particular set of columns then, to keep it simple, customised the list's native forms via Power Apps.

There are other columns in the application, but these are the minimum needed to make the Get Employee ID function work.

The key columns were:

• Timeslot (Text)
• UserName (Name)
• PhotoFileName (Text)
• Location (Text)
• Date (Date)

This is what the Gallery looks like ... just one timeslot added for demo purposes.

To make the booking form available to fellow employees, I created a Power Apps Gallery and set it up to display the vacant sessions which colleagues could reserve by clicking on "Book this timeslot" (a link to the list's EditForm). Once the Timeslot was booked, I made it invisible by having the Gallery check whether the PhotoFileName cell in the list was empty. I may discuss the building of the Gallery in a future blog if anyone is interested.

So, to get started with customising the List's EditForm, let's click on Integrate and select from Power Apps the Customize forms option from the drop-down's flyout menu.

This is pretty simple customisation, so no need for a Canvas App.

Before starting an customisation on the form, I needed to add Active Directory as a datasource, because that's where the employeeId is stored.

Add data, then select Office365Users directly.

I didn't want our registrants to mess with the Date or Timeslot fields, because they're fixed for each Photography session. So I added a Text Label to the DataCard and made it a concat of the Timeslot and the Date, separated by a comma, like this:

DateTime_Label.Text = 

Concatenate(
Text(DateTimeValue(ThisItem.Date), "dd/mm/yyyy"),
", ",
ThisItem.Timeslot
)

To display the Time and Date, add a Text Label and use the Concat function to join the two.

Next, I set up the UserName field to get the email of the current user - I'm going to use this value in the Flow that will retrieve the employeeId:

UserName.Default = Office365Users.MyProfile().Mail

Then I added a Text label to the PhotoFileName field, to start building the final filename for the image that will be added to the directory once the Photographer's done his work. This will be in the format:

FirstName_Surname_

I did this by adding this script to the Text value of the label:

Photofile_Label.Text =

Concatenate(
Office365Users.MyProfile().GivenName,
"_",
Office365Users.MyProfile().Surname,
"_",
vEmpID.empid
)

The value for the variable "vEmpID" will be generated by the Flow. We're just setting up the form to receive the value when the Flow delivers it.

The UserName field captures the email of the current user and the PhotoFileName field compiles a string of the user's name and their Employee ID.

Finally, for this part of the process, set the Update value of the PhotoFileName DataCard to fetch its value from the PhotoFile_Label.

PhotoFileNameDataCard.Update = Photofile_Label.Text

We'll come back to the EditForm shortly, but now let's set up the Flow ...

ADD AN EMBEDDED FLOW TO THE FORM

So the trick to fetching the current user's employeeId value is to add an embedded Flow to the PowerApps form. This was quite a revelation to me, but a welcome one. To embed a Flow in PowerApps all you need to do is to click on the Power Automate icon in the PowerApps left-hand column. Then, give the Flow a name and start building it, the same way you would in the Power Automate application.

Each Flow you create in your PowerApps environment will need a unique name.

Once you have created the Flow, you'll need to edit it to add the functions, so click on the ellipsis then select Edit from the flyout menu.

I called my Flow "GetIDPB", because I had another Flow in my PowerApps environment called "GetID" ...

The first item you'll need to add to your Flow is the PowerApps function. This is what will take the current user's email address and use it to retrieve the corresponding employeeId. Add "Email" as the Input type and put the Form field value in the adjacent field, like this:

The name of the form field that holds the current user's email address will be different in your form.

Next, we're going to add the function, Get user profile, click on the Advanced option and add the output from the previous function into the User (UPN) field and add "employeeid" into the Select fields field.

This is the step that goes off and finds the Employee ID.

Now we add a Compose function and make the Input the Output from the previous Get user profile function.

I'm not exactly sure what this step is doing, but just go with it ...

For me, the Dynamic Content menu didn't offer me the Output from the Get user profile function, so I had to add it as an expression, like this:

outputs('Get_user_profile_(V2)')?['body/employeeid']

The next step is optional, but I found it handy during testing to make sure the Flow was triggering when I needed it to and that the Employee ID was being captured, like this:

You can always remove the Send an email function when you're happy the Flow is performing as expected.

We're going to hold the Employee ID in a variable, so we can deliver it back to the Power App form, so let's initialise one.

No need to populate Value, we'll assign one to the variable in the next step.

The last step is to deliver the result back to the Power App form, so that requires the Respond to a PowerApp flow function. Just add the variable name and set its value to the output from the Compose function. It does seem like overkill because I added the same value for the variable in the next step, as well ...

We're setting the value of the variable in this step and sending it back to the PowerApps form.

And that's it ... we're ready to return to the Form and make sure the PhotoFileName field is ready to receive the value for the Employee ID.

But don't forget to Save your Flow first.

AND ... BACK TO THE FORM

I'd already set up the PhotoFileName field to receive the value from the EmpID variable once the Flow had run. The next challenge was to come up with a way to trigger the Flow.

In most scenarios, we ask the user to make some sort of change to the form, and we can usually piggyback on that change to trigger other actions. But in this case, I just wanted the person booking the session with the photographer to check that the timeslot was correct and then click the Save button. The rest I wanted to happen automatically and invisibly.

My first thought was to use a Timer ... so I added one to the Form and set it up like this:

Timer.Duration = 500  --  in milliseconds
Timer.Start = false
Timer.Autostart = true
Timer.OnTimerEnd = Set(vEmpID, GetIDPB.Run(DataCardValue7.Text)

Adding a Timer button seemed like the right choice, but at first it wouldn't trigger the Flow.

But that didn't work. For some reason, the Timer button would not launch the command that starts the Flow running. I tried setting the Start function to "true" but that didn't help either.

After some minutes of deep thought I came up with a solution. Swap the actions of the Timer button and the Save button.

On the Timer button I set the Autostart value back to "false". Then I gave the Start function the value of (variable) "vStart". Finally, I set the OnTimerEnd value to what I'd originally put in the Save button:

SubmitForm(SharePointForm1);Navigate(FormScreen_Thanks,ScreenTransition.Fade)

And on the Save button, I changed the OnSelect value to:

Save.OnSelect = Set(vEmpID, GetIDPB.Run(DataCardValue7.Text));Set(vStart, true)

... note the addition of the command that sets the vStart variable to "true".

Now when I clicked on the Save button, the Flow was triggered, the Employee ID was retrieved, the Flow sent me an email containing my Employee ID and the Form updated the SharePoint List field PhotoFileName with my First Name, Surname and Employee ID.

Satisfied that all was working as expected, I could now hide the form's PhotoFileName and UserName fields, along with the Timer button.

After hiding the fields and functions the user doesn't need to see, this is what the final triggered EditForm looks like.

The one final test to make sure all was working before removing the Send an email action from the GetIDPB flow ...

After clicking Save, the SharePoint list will be updated to look like this.

And that's it. Maybe in the future Microsoft will make this process a bit simpler, but until then, this should suffice.

I hope this helps someone.

Passing SharePoint List values from one list to another

I WAS LOOKING TO PASS LIST VALUES FROM ONE LIST TO ANOTHER in Power Apps, and it was quite a bit trickier than I imagined.

The company I work for has a private box at one of the big London concert venues that is used for entertaining corporate clients. My colleagues in the Events team manage the use of the box, but the process had become mired down in a maze of emails and Excel spreadsheets. I was asked if there was a better way to manage requests to use the box.

We already had a SharePoint classic calendar which was populated with the forthcoming events at the venue, but I wanted to create an application form that could manage the request process end-to-end.

The first barrier I ran into is that you can't customise the form-set for a classic calendar using Power Apps. And I wasn't able to see if using SharePoint Designer was an option, as my SPD stopped working a few months back (I've no idea why). So I had to come up with another way.

I didn't really want to get rid of the SharePoint calendar, because a lot of work had been invested in it and it is a pretty good way for our requestors to see a simple layout of forthcoming events ... so I wondered if I could create a second SharePoint list to manage the requests to use the box. It would all hinge on whether I could send some data from the calendar to the secondary Requests list. I was thinking ... query strings!

THE SOLUTION

The first step was to see whether I could send data to a Power Apps form in a query string. So I set up the Requests Management list and made sure I had a DateTime field to hold the Event's start time from the Calendar ("Start Time"). Both Lists would use the Title column to pass the name of the event.

So, to see whether it would work, I built a link, including the query string, using the simple canvas app I built to manage the Requests list, like this:

https://apps.powerapps.com/play/[PowerAppID]?tenantId=[tenantID]&Event=[Title]&Date=[Start Time]

I knew from previous Power Apps I'd built that the Date value would be passed in the format "2022-11-06T18:00:00Z", but there would be no need to try to reformat that at this stage.

Then, I needed to prepare the fields in the recipient form to receive the data from the query string. To do that I set the Event field in the Power Apps form to:

Event_DataCardValue1.Default = Param("Event")

... and the Date field to:

DataCardValue2.DefaultDate = Param("Date")

But when I pasted the constructed URL into the browser and hit Return, the result wasn't quite what I was looking for.

The Event value passed just fine, but the Date ... well, all I got was the date.

A quick search of Google suggested that a Power Apps Date field can't display the time value from a DateTime field. Quite why Microsoft thought that was a good idea, I couldn't say. But it left me in a bit of a bind. Because sometimes, concert venues have more than one show on a particular date and I needed to understand which show the Requestor was asking for.

One partial solution I found via Google was a suggestion that to fill in a time value in a Power Apps form, I could add an invisible dropdown, pre-populated with hour values, to the DateTime DataCard and then have the query string value select the appropriate hour value from the drop down to display. Sounded a bit convoluted, but I thought I might be able to adapt it to my needs.

So, I added the dropdown, then put this in its Items field:

["09","10","11","12","13","14","15","16","17","18","19","20"]

I figured I'd just put the hours that I knew would be likely to turn up in the query string - no show would start before 09.00 hours or after 20.00 hours.

Then I made the Hours dropdown's Default value:

ddHours.Default = Text(DateTimeValue(Param("Date")), "hh")

... and tried again. Success!

That was easier than I thought ... but then came the Minutes field. Using a similar approach, I added a dropdown for the Minutes value into the DateTime DataCard, and set it up like this:

Putting this in the Minutes dropdown's Items field:

["00","11","15","30","45"]

... and setting the Mins dropdown's Default value to:

ddMins.Default = Text(DateTimeValue(Param("Date")), "mm")

But it didn't work.

A bit more frantic Googling and I came across this variation for the Default field:

ddMins.Default = Minute(Param("Date"))

Ah-ha! Success! This one worked. All I had to do next was figure out the Update value for the parent DataCard and we'd be in business ...

So, in the same Google search I found a formula for updating the DateTime column in the Requests Management list. It looked like this:

EventDate_DataCardValue1.Update = DataCardValue2.SelectedDate + Time(Value(ddHours.Selected.Value), Value(ddMins.Selected.Value),0)

I'm not entirely sure what's going on here, but the first bit fetches the Date value that's been passed to the form by the query string. The second bit adds the Hours value from the invisible hours dropdown and the third bit adds the Mins value from the invisible mins dropdown. The bit I'm not sure about is the zero at the end (could it be the value for seconds?) ... but never mind, it works!

AND ... BACK TO THE CALENDAR

So, the other part of this problem was to create a link in the original source calendar that would send our intrepid requestor to the Power Apps Request form.

I've used Power Platform to create a dynamic link before and documented it here. So my first thought was to see if this could be adapted to generate a dynamic link that would be formatted the same way as my manually-created test link above. Just to remind you, the link needed to look like this:

https://apps.powerapps.com/play/[PowerAppID]?tenantId=[tenantId]&Event=[Title]&Date=[Start Time]

But first create a new field in the source calendar, Register, and make it a "Hyperlink or picture field".

Next, as in the earlier post, I thought I could create a Flow in Power Automate that would generate a dynamic link when a new Calendar entry was added.

I used a "When an item is created or modified" flow, because I figured that it would be useful if the time of an event changed, the administrator could make the change, then delete the existing link, and a new link would be generated.

So, to build the dynamic link in the Flow, we're going to use the Send an HTTP Request to SharePoint action:

The settings in this Action should be:

Set Method to:

POST

Set Uri to:

_api/web/lists/GetByTitle('Calendar')/items([ID])

... where the [ID] is replaced with a dynamic ID look up.

Set Header to:

{

   "Content-Type": "application/json;odata=verbose",

   "X-HTTP-Method": "MERGE",

   "IF-MATCH": "*"

 }

And finally, I set Body to:

{'__metadata': {'type':'SP.Data.HRBookSlotListItem'},'Register':

 {'Description': 'Book this slot',

 'Url': 'https://apps.powerapps.com/play/[PowerApps ID]?tenantId=[tenantId]&Event=[Title]&Date=[Start Time]'}

}

BUT ... this only nearly worked. What I found on testing was that the dynamic link generated wasn't quite right. Any wordspaces from the Title field were being replaced with a "+" sign. Random! Why not "%20", which is the usual replacement for a word space in a URL? Who knows?

So I figured what I would do is write the Title value to a variable then replace the + symbol with the more functional encoded wordspace ("%20"). Here's how I did it.

First, Initialise a variable to hold the Title value.

Then, initialise a second variable to process and hold the transformed Title value.

Here's the formula that we use to scan the Title value and replace the open word space with the encoded "%20":

[

  {

    "Old": " ",

    "New": "%20"

  }

]

Next, I added a condition to make sure that the new Register field in the calendar was empty:

In the Yes branch of the Condition, we need to add an Apply To Each container, so we can cycle through the Title values and replace the wordspace with "%20", like this:

We place the vReplace variable in the Select an output from a previous step.

Then we add a Compose action and insert this expression:

Inputs.fx = replace(variables('vTitle'), item()?['Old'], item()?['New'])

Finally, add a Set variable action and select vTitle in the Name field and add Outputs from the Compose action in the Value field.

Next comes the Send an HTTP request to SharePoint that we already configured earlier, but now we've replaced the dynamic Title value with the vTitle variable.

Now when you add a new event to the event calendar, the appropriate dynamic link is generated and added to the Register field in the Calendar, like this:

That's pretty much it. The Event Name and the Event DateTime get passed from the original calendar item, via the Power Apps form, to the Requests Management list. Once a new instance is created in the Requests Management list, a different Flow takes care of routing the request to the Department Head for approval, and finally informing the Requestor of the status of their request.

I hope that helps someone.

Next: More query string shenannigans

Where did that Contoso dummy content come from?

HERE'S A THING ... an internal client of mine was working on a modern Experience site, and some of her navigation links were taking her to a page of dummy Contoso content. Like this:

Trying to Google for things like "Contoso content" was no help at all ... so I needed to figure this out by myself.

I wondered if there was a dummy or template page lurking in the SitePages library of the site, but I couldn't see anything. When I looked at the URL in the browser address window, I saw a long URL, but no sign of a page name.

https://xxx.sharepoint.com/sites/DataMI/?xsdata=MDV8MDF8fDVjYTA2ZjVhN2MwNjQyMWYzMDcxMDhkYjA0MzAwZDg3fGNlNTZmYWU2MDU1ZDRjOWZiNmM5OWQzNDE1MDZhNDkxfDB8MHw2MzgxMDgzNzY2MzQ5NjIwMzN8VW5rbm93bnxWR1ZoYlhOVFpXTjFjbWwwZVZObGNuWnBZMlY4ZXlKV0lqb2lNQzR3TGpBd01EQWlMQ0pRSWpvaVYybHVNeklpTENKQlRpSTZJazkwYUdWeUlpd2lWMVFpT2pFeGZRPT18MXxNVFkzTlRJME1EZzJNamN3T1RzeE5qYzFNa

(I've trimmed off several lines of this for space-saving.)

So I started nosing around in the navigation links and found that the links that produced this behaviour were simply pointing at the top level of the site:

https://xxx.sharepoint.com/sites/DataMI/

... with or without the closing slash.

Of course, in a Modern Experience site, you can't have a navigation link that doesn't have a URL ... so I switched those links to labels, which solved the problem temporarily.

If I pasted the URL of the actual Home page into the browser address window, I got the actual home page:

https://xxx.sharepoint.com/sites/DataMI/SitePages/Home.aspx

And if I clicked on the Home link in the top navigation menu, I also got the Home page.

... but no great surprise, as this also had the full link to Home.aspx.

I began wondering if this was something to do with the setting of the default page for the site. So on a hunch, I found Home.aspx in the SitePages library and set it as the site default page.

Problem solved.

All I can think of is that at some time in the site's past, someone either deleted the default page (I don't know how, as SharePoint won't let you delete a designated default page) or found some way to NOT have a default page at all. The fact that the Home navigation link had the full URL of the Home page tends to support both those ideas.

Anyway, if you see this behaviour, now you know why.

I hope this helps someone.

Power Apps: Add person to SharePoint Name field via dropdown in Canvas App form

POWER APPS AND SHAREPOINT IS AN IMMENSELY POWERFUL COMBINATION that allows you to construct time-saving automated applications that will save your colleagues valuable time when following business processes. But you knew that already, right?

I was recently working on a business process that required the Requestor choose an Approver from a list of authorised Approvers. Each area of the business had a different set of Approvers, and I wanted to make sure that the Requestor wasn't able to select the wrong Approver for their business area.

What's the problem, I hear you shout. Just whack in a ComboBox or a DropDown and off you go ... and, in truth, that's how I first approached the problem.

In addition to the main list which would hold the requests ("FAC") I created a lookup list ("FAClob") with two columns - a simple text column (I just used the existing Title column) to hold the Line of Business values and Person or group column to hold the corresponding Authorisers.

Here's the lookup list. Normally, I'd order by the Title column, but here I've ordered by the Underwriter column to show that the Title column contains an assortment of duplicate values.

I did then try to integrate the lookup list into the main list by adding a lookup column and pointing it at my LoB/Authorisers lookup list ... but quickly realised that was not the right approach. Far better to handle the looking up in the actual Canvas App that will handle the Requestors' submissions to the main list. So I deleted lookup column in the main FAC list and instead added a simple text field for the Line of Business and a Person or group field for the Authoriser.

Here's the corresponding columns in the main FAC list.

So next I switched over to Power Apps, refreshed the DataSources to ensure that my app was looking at the latest versions of the lists, then set about adding the two new FAC fields to the New Form I was working on ...

Here's the new fields with the dropdowns already in place.

With the fields in place in the Canvas App Form, I then set up the Line of Business DataCard. I added a DropDown to the DataCard and renamed it ddLoB. Then, switched to Advanced in the dropdown's Property panel and set the Items value to:

ddLoB.Items = Distinct(FAClob,Title)

and

ddLoB.Value = Result

You can leave the pane's Default value as "1", the Defaults default value, as changing it doesn't seem to do anything.

The "Distinct" ensures that we only display one of each of the LoB values from the lookup list.

Next, we need to set up the DataCard by editing a couple of fields in the DataCard's Advanced Properties pane ...

We need the single quotes around '1-LoB' because it contains a hyphen.

The default value should already be set to ThisItem.'1-LoB' (the single quotes are needed because I called my SharePoint List column 1-LoB, denoting this is a field for Form 1 in the process), but we have to change the Update value to:

1-LoB_DataCard1.Update = ddLoB.SelectedText.Value

Right ... that's the Line of Business field taken care of. Next, the Authoriser field.

What I need to do here is to make sure that when a requestor selects a Line of Business from the dropdown, the choice of authoriser is limited to the corresponding names from the lookup list. So I added a dropdown ("ddAUW") to the Authorisers DataCard and set these fields in the Properties/Advanced window ...

ddAUW.Items = Distinct(Filter(FAClob,Title = ddLoB.SelectedText.Value),Underwriter.DisplayName)

ddAUW.Value = Result

The Authorisers dropdown's Items formula is a little more complicated because a Person or Group field holds a table rather than a single value, and we have to tell the dropdown which part of the table to return.

Then I turned my attention to the Update value in the DataCard ... and initially I fell into the trap of thinking I could just update the SharePoint list with a value from the Authoriser dropdown. Like this:

1-AuthorisingUW_DataCard1.Update = ddAUW.Result

I was just using a submit button to send the form's values to the SharePoint list, with the simple command:

Submit_Button.OnSelect = Submit(Form1)

But, of course, it didn't work. And, of course, it couldn't be as simple as that, could it?

So I asked my colleague Ernani if he had any idea what was going on. He suggested switching out the simple Submit button for a patch function, and even went to the trouble of writing the patch formula for me ... which was great, and it worked. But I like simplicity, and I felt that as I developed the forms needed for the whole application, having complex patches instead of simple Submits might become an albatross around my neck.

So, I wondered if I could take the section of the patch formula that sends the Authoriser details to the SharePoint list and instead pasted that code into the DataCard's Update action, that might serve the same function, while still allowing me to use a simple submit button. Here's the formula:

{  

'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",  

Claims: "i:0#.f|membership|" & LookUp(

    FAClob,

    Underwriter.DisplayName = ddAUW.SelectedText.Value,

    Underwriter.Email

),

DisplayName: "",

Email: LookUp(

    FAClob,

    Underwriter.DisplayName = ddAUW.SelectedText.Value,

    Underwriter.Email

)

}

And here's what it looks like in place:

This is what the Advanced properties looks like after adding the formula.

Then I just checked that the Default value of the DataCard was:

1-AuthorisingUW_DataCard1.Default = ThisItem.'1-AuthorisingUW'

And you know what? When I tested it, it worked!

There's a lot more to this particular form than what I've outlined above, but for me, the key breakthrough was the updating of the SharePoint Person and Group field from a Power Apps dropdown.

Here's what the final cascade looks like ... select UK Casualty in the Line of Business dropdown and the Authorising Underwriters are restricted to those who can sign off requests for that business area.

I hope this helps someone.