Build Flow with 2 Sharepoints

[Laila_Morocco]

I have 2 linked sharepoints as below (I make it simple)

I want:
1. STEP1: when a user changes status on List 1 to a 'specific value' 

2. STEP2: go and check in List 2, if this course_ID type = "a specific value"

3. STEP3: If yes Notify the manager by email 

Could anyone help me with STEP2 ?

For now I can do the STEP1, I can do that

below condition always return false

Thanks in advance

[v-yetonggu-msft]

Hi @Laila_Morocco ,

Please show screenshots of the content of your two SharePoint lists. Private information can be coded. I am confused by your simple relationship diagram.

Best Regards,

Sunshine Gu

[Laila_Morocco]

Hello Sunshine Gu
The relationship is simple, only 1 column is common between the 2 lists

Column From List 1 Enrollment_Course_ID is linked to List 2 Course_ID column  as explained below

I want a flow where, when the users change the status to 'Favorable' of an item in List 1, go and check if this item's  Enrollment_Course_ID in List2, its Type is equal to "Driving License" As below

As you can see, If the user changes the status to Favorable in 2 items in List 1 - I want to send an email only for the second item, because its Course_ID type is equal to "Driving Licence" in List 2

I hope you'll understand

[Laila_Morocco]

I did this, but the result is false

The issue is here:
After checking if the status = to Favorable, I don't know how to do the following Steps

1. STEP1: when a user changes status on List 1 to a 'Favorable' 

2. STEP2: go and check in List 2, if this course_ID type = "Driving License"

3. STEP3: If yes Notify the manager by email 

Thanks in advance

[v-yetonggu-msft]

Hi @Laila_Morocco ,

OK, I understand.

Please check this test:

My SharePoint List1:

My SharePoint List2:

I don't know which column the email address is stored in your list 2, in my test I stored it in the person column.

My Flow:

Course_ID/EnrollmentCourse_ID eq '@{triggerOutputs()?['body/EnrollmentCourse_ID']}' and CourseType eq 'Driving License'

length(outputs('Get_items')?['body/value'])

Best Regards,

Sunshine Gu

[Laila_Morocco]

Hello Sunshine Gu,

thanks for your great effort
First I store the person in List 1 (test1) but It's okay, same logic

When I test the first part of the flow. it's working perfectly, I can get a true result when I change the status to Favorable but..

When I add the second part; I'm getting these errors:

In Get Items

I'm getting this error

Result:

In Condition (Condition 1 in your case): Action Failed

In Get Item: Column Course_ID does not exist

Could you kindly support on this

[v-yetonggu-msft]

Hi @Laila_Morocco ,

It was my mistake.

I did another test for your reference.

In my scenario:

My SharePoint List1:

My SharePoint List2:

My Flow:

Course_ID eq '@{triggerOutputs()?['body/EnrollmentCourse_ID/Value']}' and CourseType eq 'Driving License'
length(outputs('Get_items')?['body/value'])

 Best Regards,

Sunshine Gu

[Laila_Morocco]

Hello again, thank you so much for the correction and your effort,

I guess the issue in on Filter query. Here is the result

I copy this into Filter Query

Course_ID eq '@{triggerOutputs()?['body/EnrollmentCourse_ID/Value']}' and CourseType eq 'Driving License'

I'm getting this error:

Filter Query after doing a Test

Test Result is same as yesterday, in In Get Item Body: Column Course_ID does not exist
While it exists

Maybe do issue is only on Filter Query Field, what do you suggest ? thanks in advance

[v-yetonggu-msft]

Hi @Laila_Morocco ,

To refer to the internal names of the columns in the expression, look at the internal names of the 'EnrollmentCourse_ID' and 'Course_ID' columns in the SharePoint list.

Please see the link below for details:

SharePoint Column/Field Display Name vs Internal Name - SharePoint Diary

Best Regards,

Sunshine Gu

[v-yetonggu-msft]

Hi @Laila_Morocco ，

I'm confused as to why your OData Filter shows different types of matches?

Course_ID is a text column, EnrollmentCourse_ID if it is a lookup column, then its value is also text,

if CourseType is a single choice column, then its value is also text, why does it show a type mismatch, I think you should check your Course_ID, EnrollmentCourse_ID, what are the types of CourseType, and then tell me.

Best Regards,

Sunshine Gu

[Laila_Morocco]

Thanks for your effort, for the internal names 

 But I'm confused about OData Filter.. I will try using Course_ID0 

You are correct regarding Columns type

Course_ID is a text column, EnrollmentCourse_ID  is a lookup column, CourseType is a single choice column

EnrollmentCourse_ID 

CourseType

I will write the results

[Laila_Morocco]

Hello again,

Good progress now I have no issue on Get items Step

Here is the Code View of the first Condition

{
  "type": "If",
  "expression": {
    "and": [
      {
        "equals": [
          "@body('Get_changes_for_an_item_or_a_file_(properties_only)')?['ColumnHasChanged']?['Status']",
          "@true"
        ]
      },
      {
        "equals": [
          "@triggerBody()?['Status']?['Value']",
          "Favorable"
        ]
      }
    ]
  },
  "actions": {
    "Get_items": {
      "type": "OpenApiConnection",
      "inputs": {
        "parameters": {
          "dataset": "https://siemensgamesa.sharepoint.com/teams/OG0101F",
          "table": "580016ac-8b99-4590-a184-ff8e99635af0",
          "$filter": "Course_ID0 eq '@{triggerOutputs()?['body/EnrollmentCourse_ID/Value']}' and CourseType eq 'Driving License'"
        },
        "host": {
          "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
          "connection": "shared_sharepointonline",
          "operationId": "GetItems"
        }
      },
      "metadata": {
        "operationMetadataId": "47b8de1c-aa7b-4e3f-a754-91af630d6c83"
      }
    },
    "Condition_1": {
      "type": "If",
      "expression": {
        "and": [
          {
            "greater": [
              "length(outputs('Get_items')?['body/value'])",
              0
            ]
          }
        ]
      },
      "actions": {},
      "else": {
        "actions": {}
      },
      "runAfter": {
        "Get_items": [
          "Succeeded"
        ]
      },
      "metadata": {
        "operationMetadataId": "34eb84ad-45cf-4d79-ae5f-9ec45dc4ef11"
      }
    }
  },
  "else": {
    "actions": {}
  },
  "runAfter": {
    "Get_changes_for_an_item_or_a_file_(properties_only)": [
      "Succeeded"
    ]
  },
  "metadata": {
    "operationMetadataId": "f92923cf-6c52-4890-9a31-0639bd0f8818"
  }
}

A question it Normal to get always this error in OData field when Ic opy your code or when I try to write it manually ? am I doing something wrong ?

Get Items

This is the EnrollmentCourse_ID, is it ok ?

Sorry and thanks again
Kind Regards
Laila

[v-yetonggu-msft · Accepted solution]

Hi @Laila_Morocco ,

Please try put this expression in Get items Filter Query:

Course_ID0 eq '@{triggerOutputs()?['body/Course_ID/Value']}' and CourseType eq 'Driving License'

Because your 'EnrollmentCourse_ID' Column Internal Name is 'Course_ID',so we need quote 'Course_ID' internal name in expression.

Best Regards,

Sunshine Gu

[Laila_Morocco]

Good,

Get Items is now working, but I still have issues on the second condition

1st Condition

2nd Condition: Unable to process template expressions for action ...

Code View

{
  "type": "If",
  "expression": {
    "and": [
      {
        "greater": [
          "length(outputs('Get_items')?['body/value'])",
          0
        ]
      }
    ]
  },
  "actions": {},
  "else": {
    "actions": {}
  },
  "runAfter": {
    "Get_items": [
      "Succeeded"
    ]
  },
  "metadata": {
    "operationMetadataId": "34eb84ad-45cf-4d79-ae5f-9ec45dc4ef11"
  }
}

Thanks