Issue with several conditions

[smorph]

Hey everyone,

I'm stugling with a condition within one of my newly creates flows.

What should it do:
I'm getting al projects on Project Online and check first if they got an SharePoint list item id.

If that is true my flow should get that specific item and check if one of the defined field content is different to the ones I got from project online.

Only if one of the field content is different the flow should update the SharePoint List item.

All others should be skipped.

After running it all items will be updated. I checked several projects and the field content was exactly the same but it still was in the update routine.

Does anyone have any idea where my mistake might be?

[royg]

Hi @smorph,

It's really hard to analyze the image. Can you please Copy the Condition to clipboard and paste the contents here?

You probably compare SP Item field with the corresponding Project field but need to select the SP Item field/value.

Hope this helps  

[smorph]

Hey @royg ,

thanks for getting back.
Here the code, hopefully it helps.

{
  "type": "If",
  "expression": {
    "or": [
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/Title']",
            "@items('Apply_to_each')?['ProjectName']"
          ]
        }
      },
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/SOLL_x002d_Zustand']",
            "@items('Apply_to_each')?['SOLLZustand']"
          ]
        }
      },
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/avisiertes_x0020_Enddatum']",
            "@items('Apply_to_each')?['ProjectFinishDate']"
          ]
        }
      },
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/Auftraggeber/DisplayName']",
            "@items('Apply_to_each')?['Auftraggeber']"
          ]
        }
      },
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/Manager/DisplayName']",
            "@items('Apply_to_each')?['ProjectOwnerName']"
          ]
        }
      },
      {
        "not": {
          "equals": [
            "@outputs('Get_item_-_Listeneintrag_abrufen')?['body/Phase/Value']",
            "@items('Apply_to_each')?['Phase']"
          ]
        }
      }
    ]
  }

After checking the conditions I was only able to find the field/value, as suggested for the field phase, for that one I already used it.

[eliotcole]

Hi, @smorph, I'm wondering if you can't achieve what you want using an ODATA filter on your original HTTP request (assuming that it is from a list/library), as that would eliminate the need to perform the condition action you are performing, perhaps.

Something like the following on the end of your original HTTP request might filter it to only items with non-null 'VorhabenlisteID' values:

?$filter=VorhabenlisteID ne null

Then you would only have results that have that data.

Once you have that data, you could then just run your Apply to each / For each action on the results.

I suspect that you are potentially having issues when some of the data that you are trying to input is not present.

To get around this I would suggest that you use a Select to make entry bodies that you will then use in an HTTP update inside the Apply to each / For each action which will run on the results of the new Select action. This way it only updates the fields that it has data for.

[smorph]

Hey @eliotcole ,

the filter idea might be a solution, but not at that step you suggested, there I'm not having any issues.
Why do i need there a condition action:
If "VorhabenlisteID eq null" is true, the flow has to create a new list item within my SharePoint list.

If it's not true then the flow should check if any of the available item fields are different to the ones I'm getting via my http request, and then update these items.

Let me check the filter idea.

[royg]

Hi @smorph,

What immediately catches my eye is date comparison. Getting the dates from Project Online will return the date + time of the entities e.g. if a Task/Projects ends on 2023-01-30, it will return 2023-01-30 17:00.

Not sure what you store in SharePoint but if this field is configured not to store time, you compare a DateTime with a Date and that returns false!

try formatting both dates to yyyy/mm/dd and see what happens.

Also, you might want to lower-case when compraing string values (If for example the Project Owner names are not the same values stored in SharePoint).

Hope it helps 

[smorph]

@royg ,

yep seen that as well with the filter query suggested by @eliotcole .
But I'm not able to use formatdatetime() on that specific field because it's then missing...

Without any expression I'm able to choose it but within a expression there are only the "Body" and "Value" returns of my parse json action available.

[smorph]

@eliotcole 

after recreating the flow I was able to create the Get Items action.

But it seemed I messed up the filter query. 🙄

There is no difference if I put the dynamic fields in ' ' or leave them.
The error message is: The $filter expression is not valid. Creating query failed

For testing purpose I changed on an already closed project the "Auftraggeber" and the both fields (item and project) are definitely different.

[eliotcole]

Start bracketing some of those conditions, there, could maybe help you, @smorph ...

I can't look at this right now in detail ... but I'm going to take a wild swing that *still* you are potentially reacting to information that might have no data.

What I'm trying to say is that putting all that inside your condition, whether it is in an ODATA filter, a condition action, or a filter action, it will fail if SharePoint don't have the information to feed it.

I'll see if I can drum you up an example ... but if there is any more of your flow design that you can slide into the original question, it could provide a little more to work with here. (sorry to ask ... no pressure!)

[eliotcole]

Also, I've just noticed that you are adding data to 'Claims' fields, @smorph ...

I believe that these will require specific information to be added in them in a specific way. You will need to be entering an array of strings which look something like this:

[
  "i:0#.f|membership|name@domain.com",
  "i:0#.f|membership|name@domain.com",
  "i:0#.f|membership|name@domain.com"
]

Those are just users, groups have a different lookielikie.

---

Also, in your initial action named 'Send an HTTP request to SharePoint - Vorhabenauflistung' is that just a simple list query, or are you actually sending an API request to Microsoft Project or Planner there?

If we could see that and/or have a (sanitised) version of what it spits out, that will really help. 👍

[royg]

Focus on the dates, that's where I'd put my money

[eliotcole]

Also, replace your "null" value in the 'Condition - Listen item vorhanden' Condition action with this... and paste this in don't type it:

@null

Sorry, also ... are the two people columns in SharePoint for single selections, or multiple selections of people?

[eliotcole]

Apologies for the double post, but it has been a little while since the last one, and I wanted to ensure that you weren't typing or something. I'm just after a bit of information so that I can accurately help you out, here. I think I have the beginnings of a solution. I require two pieces of information:

1. Peek Code On Update item action
2. Schema Of The 'Parse JSON - Projecktauflistung' action

Schema Of The 'Parse JSON - Projecktauflistung' action

Could you also include a code entry with the schema value 'Parse JSON - Projecktauflistung' action, please?

There is information which I am looking for and need to know how to address it, here. I have never seen Project Online API responses, and couldn't easily locate examples. 🙁

Peek Code On Update item - Listeneintrag aktualisieren Action

Could you also do a 'Peek code' on your 'Update item - Listeneintrag aktualisieren' action and copy the contents of the parameters key value, please? (tap the menu on the action and select 'Peek code' there)

You do not need to include the 'dataset' value, or the 'table' value. You can/should keep a copy of the table value, as that is the List ID, and you can use that always accurately call the list with HTML calls. It's better than using the list name. I usually place it in a variable at the start of any flow called 'ListIdVAR' so that I can use it in the flow.

The parameters (without dataset+table) should look something like this:

{
	"id": 7,
	"item/Title": "a",
	"item/choiceuno/Value": "Chuck",
	"item/choicedos/Value": "JESSI",
	"item/Status/Value": "New",
	"item/sharedtokenstring": "a",
	"item/Pa_x00ed_s": "a",
	"item/Names": "a",
	"item/Auftraggeber/Claims": "i:0#.f|membership|name@domain.com",
	"item/Assigned_x0020_to": [
		{
			"Claims": "i:0#.f|membership|name@domain.com"
		}
	],
	"item/choicea/Id": 2,
	"item/choiceb/Id": 3
}

That is one of mine.

---

Also, not for nothing, since you are not doing all of this in Select or Filter actions, you might as well separate out each thing that you want to check with its own condition.

So, instead of the full 'Condition - Eintrage unterschiedlich' action, make a condition for each thing you are checking in that condition. Because you should first be ensuring that the item is not null in the Apply to each anyway.

Then if it is due an update, update the item with just that individual piece of data. If this is going the long way around ... might as well use that to find out where your problems are.

At least this way you will quickly identify which part is the problem.

[smorph]

Hey @eliotcole , @royg ,

Thanks for your time and effort, a lot of input from your side i needed to check and test them out.

The initial idea was to use one condition, or the suggested filter option, to avoid several condition branches.

It would make the flow more complex than it in my opinion need to be.

For me it's not important only to change that one field which is different. It's ok for me to update the whole item if one or more fields are different.

After deleting the filter expression to only one I still get the same error message

The $filter expression is not valid. Creating query failed

In this testcase the both fields were equal.

The next test was to check not equal fields, that's the same error message

The $filter expression is not valid. Creating query failed

It seems to be a general issue with my filter expression.

Let's have a look into the whole flow and the branch which has the issue as request @eliotcole 

1. Reccurance
Nothign mor to say here

2. Get all Projects form Project Online via HTTP

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "dataset": "@parameters('URL-PWA (iasit_URLPWA)')",
      "parameters/method": "GET",
      "parameters/uri": "_api/ProjectData/[en-us]/Projects?$select=ProjectId,ProjectIdentifier,ProjectName,SOLLZustand,EnterpriseProjectTypeName,Phase,ProjectStartDate,ProjectFinishDate,Auftraggeber,ProjectOwnerName,VorhabenlisteID,Unternehmen&$filter=ProjectName ne 'Arbeitszeittabelle administrative Arbeitsaufgaben' and (EnterpriseProjectTypeId ne guid'@{parameters('EnterpriseProjectTypeId-Abwesenheiten (iasit_EnterpriseProjectTypeIdAbwesenheiten)')}' and EnterpriseProjectTypeId ne guid'@{parameters('EnterpriseProjectTypeId-Administrativ (iasit_EnterpriseProjectTypeIdAdministrativ)')}' and EnterpriseProjectTypeId ne guid'@{parameters('EnterpriseProjectTypeId-Test (iasit_EnterpriseProjectTypeIdTest)')}')",
      "parameters/headers": {
        "accept": "application/json"
      }
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
      "connection": "shared_sharepointonline",
      "operationId": "HttpRequest"
    }
  },
  "runAfter": {},
  "metadata": {
    "operationMetadataId": "xxxx"
  }
}

Within I filter already one specific project and a few project types out.

3. Parse JSON of above action

{
  "type": "ParseJson",
  "inputs": {
    "content": "@body('Send_an_HTTP_request_to_SharePoint_-_Vorhabenauflistung')",
    "schema": {
      "type": "object",
      "properties": {
        "odata.metadata": {
          "type": "string"
        },
        "value": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "ProjectId": {
                "type": "string"
              },
              "EnterpriseProjectTypeName": {
                "type": "string"
              },
              "ProjectFinishDate": {
                "type": "string"
              },
              "ProjectIdentifier": {
                "type": "string"
              },
              "ProjectName": {
                "type": "string"
              },
              "ProjectOwnerName": {
                "type": "string"
              },
              "ProjectStartDate": {
                "type": "string"
              },
              "Unternehmen": {
                "type": "string"
              },
              "Auftraggeber": {
                "type": "string"
              },
              "SOLLZustand": {},
              "Phase": {
                "type": "string"
              },
              "VorhabenlisteID": {}
            },
            "required": [
              "ProjectId",
              "EnterpriseProjectTypeName",
              "ProjectFinishDate",
              "ProjectIdentifier",
              "ProjectName",
              "ProjectOwnerName",
              "ProjectStartDate",
              "Unternehmen",
              "Auftraggeber",
              "SOLLZustand",
              "Phase",
              "VorhabenlisteID"
            ]
          }
        }
      }
    }
  },
  "runAfter": {
    "Send_an_HTTP_request_to_SharePoint_-_Vorhabenauflistung": [
      "Succeeded"
    ]
  },
  "metadata": {
    "operationMetadataId": "xxxx"
  }
}

4. First Condition to check if any Project doenst' have a VorhabenlisteID, which is there to connect project and list item

@eliotcole I changed the eq condition to your suggested one, but that wasn't the issue.

The "yes" branch works very well. It's the "no" branch.

5. Need to get first all Items in that list to use fields later as dynamic content

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "dataset": "https://xxxx.sharepoint.com/teams/xxxx",
      "table": "xxxx",
      "id": "@items('Apply_to_each')?['VorhabenlisteID']",
      "view": "xxxx"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
      "connection": "shared_sharepointonline",
      "operationId": "GetItem"
    }
  },
  "metadata": {
    "operationMetadataId": "xxxx"
  }
}

Here the flow still works very well, except of that suggested issue form @eliotcole , if a field has no value the flow stops here for that specific item. But that's another story which has nothing to do with my main issue and can be solved very quickly.

6. Filtering all Items on which one or more field values are different to the project online ones

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "dataset": "https://xxxx.sharepoint.com/teams/xxxx",
      "table": "xxxx",
      "$filter": "?$filter= @{body('Get_item_-_Listeneintrag_abrufen')?['Auftraggeber']?['DisplayName']} ne @{items('Apply_to_each')?['Auftraggeber']}",
      "view": "xxxx"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
      "connection": "shared_sharepointonline",
      "operationId": "GetItems"
    }
  },
  "runAfter": {
    "Get_item_-_Listeneintrag_abrufen": [
      "Succeeded"
    ]
  },
  "metadata": {
    "operationMetadataId": "xxxx"
  }
}

The code is now from my last testing but it's similar to the above with mor fields.
Doublecheck the "Auftraggeber" values and already used "DisplayName" instead of "Claims". @royg I even changed the date format as suggested but I will get the above mentioned error message.

And here the whole flow stops.

[royg]

Hi @smorph,

When filtering SharePoint Get Items action, you don't need to provide the "?$filter=" initial, please remove that part

[smorph]

@royg done as suggested, the error message changed now to "The expression is not valid".

{
  "dataset": "https://xxxx.sharepoint.com/teams/xxxx",
  "table": "xxxx",
  "$filter": "[Name A] ne [Name B]",
  "view": "xxxx"
}

{
  "status": 400,
  "message": "The expression \"[Name A] ne [Name B]\" is not valid. Creating query failed.\r\nclientRequestId: xxxx\r\nserviceRequestId: xxxx"
}

It's the same with any other field.

[eliotcole]

Hi, @smorph, part of my solution (when it comes) will require a couple of things ... so if you can get those it would really assist. 🙂

Project Data

Owner ID

If you could add 'ProjectOwnerId' to the select you are using in 'Send an HTTP request to SharePoint - Vorhabenauflistung' action, that would really help.

Auftraggeber ID

Also, if you are able to get the ID of the user defined in this field, that would be great. My mistake, I just did a search on this ... I thought this was a user field. If this is

List Data

List ID

Also, if you could retrieve the List ID for the target list and store it in a variable named the following that would also be great:

ListIdVAR

You can get this *really* easily, just open up the list settings in SharePoint, and if you look at the URL it will end with either of the following:

• List={LIST_ID}

• List=%7BLIST_ID%7D

We just need the bits between the '{' + '}' or between the '%7B' and the '%7D' in the new variable. 👍

I will detail all this again inside my solution.

Column Types

Could you send a screenshot of the List Settings screen showing the column names and types?

I want to ensure that the data being entered is being done so correctly.

As you can see with my edits above I'm having trouble with your 'Auftraggeber' ('Client') information, in both your Project and in your list.

It is clear from your data above that:

• In Project - The 'Auftraggeber' key appears to be plain text
• In List - The 'Auftraggeber' column is either:
  • ... a 'Lookup' type column
  • ... a 'Person' type column

What I need to ensure is that this data is accurately resolved. If it is a Person column it's a lot harder with just the name to get accurate information, if it is a Lookup, then I would hope that the 'Auftraggeber' list has unique values for the client information.

[royg · Accepted solution]

Getting closer.

The OData Filter requires the field internal names on the left and text values on the right of each comparison, for example ProjectOwner ne 'Roy' .

Note - no spaces in field names!

You can get the internal names from the outputs of an unfiltered call.

[smorph]

I already worked with the internal name especially witch custom fields. But only for updating them.

I'm not sure how this will solve my issue, could you @royg  please explain a bit further?

I like to compare the project field value with item field value.

If i use like "[project_internalname] ne [smorph]" that will everytime be not true because the project field is [smorph], even if it's changed, which happens before the flow runs.

[smorph]

For the Auftraggeber there is no ID available, it's a custom field just as text.
The ProjectOwner field is a PeoplePicker, but not the one for Auftraggeber, sadly there seems to be no way to get a PeoplePicker for a custom field.

I see where you @eliotcole want to go with the User ID, but is this neccessary?
When I run my "yes" branch with creating a new item, SharePoint has no issue resolve display name to the specific claims.

I know that if there is anytime someone with the exact same display name, I would run in an issue, but that's part for the future smorph to solve.


I will add the List ID.
Awaiting your solution suggestion.