Forum Discussion
Solved
need help for lookup to choice field - SharePoint List
Hello SharePoint people,
Please I need your help with an urgent task. I have 2 SharePoint lists A and B
A: a list of aircraft contract based on aircraft name and contract status ( active, terminated a choice field)
B: a maintenance list with the aircraft name
I want to display the contract status in list B. I tried to have a calculated column in list A based on the chosen field and then to look up to list B, but it doesn't work.
Any help please? the relation between the two lists is based on the aircraft name.
Thanks,
Sabrine
sabrinekalboussi as David Mehr mentioned, one way is to to do this with a flow in Power Automate. In this example we have the 2 lists:
Next in Power Automate create a new instant cloud flow and the trigger will be a SharePoint "When an item is created or modified" trigger:
Next, we want to see if that Aircraft is in the maintenance list B, so add a Get items action (note, not Get item) and add a Filter Query. In both lists the aircraft unique registration is in the Title column, so the filter query is Title eq '{select Title from the dynamic content box}' - where the Title you select is from the When a new item is created or modified section of the dynamic content box:
But we need to take an action if there isn't an equivalent aircraft in List B. So to do this add a condition and click in the left hand field, select the Expression tab and type
equals(length(body('Get_items')?['value']),0)
in the middle field select is equal to and in the right hand type trueYou have 2 channels in the condition, If Yes and If No. So in the If Yes channel, i.e there isn't an equivalent aircraft in List B, we'll create one with the SharePoint Create item action. In the ContractStatus field select ContractStatus from the When a new item is created or modified section of the dynamic content.
But if there is an equivalent aircraft in List B then we just need to update it with the SharePoint Update item action from the dynamic content. It will wrap itself in an Apply to each (this is expected where you have a Get items action earlier in the flow). In the ContractStatus field you again select Contract Status from the When a new item is created or modified section of the dynamic content.It can take a minute or 2 for the flow to run but it will either add the contract status or update it with no problem. An example of it being updated is in the attached video.
Come back to me if you need any more information about this, if something isn't clear or if you wish to explore the Power Apps route.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
sabrinekalboussi as David Mehr mentioned, one way is to to do this with a flow in Power Automate. In this example we have the 2 lists:
Next in Power Automate create a new instant cloud flow and the trigger will be a SharePoint "When an item is created or modified" trigger:
Next, we want to see if that Aircraft is in the maintenance list B, so add a Get items action (note, not Get item) and add a Filter Query. In both lists the aircraft unique registration is in the Title column, so the filter query is Title eq '{select Title from the dynamic content box}' - where the Title you select is from the When a new item is created or modified section of the dynamic content box:
But we need to take an action if there isn't an equivalent aircraft in List B. So to do this add a condition and click in the left hand field, select the Expression tab and type
equals(length(body('Get_items')?['value']),0)
in the middle field select is equal to and in the right hand type trueYou have 2 channels in the condition, If Yes and If No. So in the If Yes channel, i.e there isn't an equivalent aircraft in List B, we'll create one with the SharePoint Create item action. In the ContractStatus field select ContractStatus from the When a new item is created or modified section of the dynamic content.
But if there is an equivalent aircraft in List B then we just need to update it with the SharePoint Update item action from the dynamic content. It will wrap itself in an Apply to each (this is expected where you have a Get items action earlier in the flow). In the ContractStatus field you again select Contract Status from the When a new item is created or modified section of the dynamic content.It can take a minute or 2 for the flow to run but it will either add the contract status or update it with no problem. An example of it being updated is in the attached video.
Come back to me if you need any more information about this, if something isn't clear or if you wish to explore the Power Apps route.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserRobElliott thanks a lot for your detailed reply ^^
sabrinekalboussi calculated columns are unsupported for lookups. See the limits in this article under "creating unique columns" Create list relationships by using unique and lookup columns - SharePoint (microsoft.com) If you can't change your existing architecture. Use a Flow (or similar EventReceiver/PowerShell) to copy the value in a supported column, or create a Power Apps app.
