Populating email field on new item from another list and based on a condition that equals true

[trishmalloy]

Hello,

I am trying to create a Flow that populates an email field when a new item is created and if "Email workouts" is set to yes.

I have 2 lists:  1) List of Clients which is where the email field is populated.  2) Create workout.  When a new workout is created and if email workouts equals yes, I want the flow to find the corresponding email address from List of Clients and populate the E-mail field based on the Client Name.

The plan is to create a button in my PowerApps app that when clicked on emails the selected workout to the client.

Here's the Flow I have set up so far.

1. When an item is created in Create workout list

2. Get items from List of Clients w/Filter Query = E-mail eq 'E-Mail' *second E-mail choses from Dynamic Content

3. Apply to each = value

4. Update item in Create workout list using ID and on the E-mail field I added E-mail from Dynamic Content.

I'm getting "BadRequest" Status code 400. 

{
  "status": 400,
  "message": "The expression \"E-mail eq ''\" is not valid.\r\nclientRequestId: 2b5f6fa3-b2d7-45e6-9eb6-48bfd8aa0fb1\r\nserviceRequestId: db07a09e-e0f8-7000-0ba3-4a33337722d5"
}

[v-yamao-msft]

Hi @trishmalloy,

Could you share more details on the lists? Do the two lists have a common value?

Could you provide a screenshot of your flow? How is the code in Filter query configured?

A screenshot would be better for understanding.

I will help reproduce this issue, please feel free post back with more details. 

Best regards,

Mabel

[trishmalloy]

Hello Mabel,

Thank you for responding!

The List of Clients list has client profile information e.g. Name, Phone, Email, etc..  The Create Workout list has a lookup field to the client name from the List of Clients list.  There is an E-mail field in each list, it's populated when a new List of Clients (client profile) is created; the field is a single line of text field.  I'm using the same E-mail site column in the Create Workout list which is empty.  It would only be populated a) If the field "E-mail workouts" is Yes and b) when a new workout is created.

[trishmalloy]

Hello,

I'm hoping someone can help me with this issue.  Do you need more information?

Trish

[ScottShearer]

@trishmalloy:

From your description, it appears as though Flow doesn't think the Email column has been populated.

Your screen shot shows that you are referencing the email column in the Workouts list in the ODATA filter.  Can you verify that the email addresses are populated? 

It looks as though there may be a space between the first single quote in the ODATA filter and the email reference from Dynamic properties.  Can you check and remove the space if it is there (and test)?

Also, can you confirm that the email address in the Workouts list is a simple text column?

Scott

[trishmalloy]

Hi ScottShearer,

Thanks for the response! 

The E-mail field is always populated in the List of Clients list.  I'm wanting to copy the email address from the List of Clients list to a person's Workout record if the condition "Email workouts = Yes".  Am I doing this backwards?

If there was a space between the first single quote, there doesn't seem to be one there now.  I'm not understanding why .... see text in red.

{

"status": 400,

"message": "The expression \"E-mail eq ''\" is not valid.\r\nclientRequestId: 1b5f5bee-bea6-4ead-b098-0ff0740ac11c\r\nserviceRequestId: 07eba39e-a0ba-7000-778f-0ae386cd2690"

}

Both email fields are simple line of text fields.  I'm using the one E-mail site column for both lists, is that okay?

[ScottShearer]

@trishmalloy:

I may have led you down the wrong path....  I forgot that the Email column on which you are using an ODATA filter is a lookup and ODATA filters are not currently supported on lookups.

Here is a link to a post that explains a work-around.

Scott

[trishmalloy]

Thanks Scott, I'll look at the workaround.  However, the actual Email field in the List of Clients list and Create workout list are both single line of text fields, not lookup.  The actual lookup field from the List of Clients list to the Create workout list is Client Name.  Does that matter?  

[ScottShearer]

@trishmalloy:

I gues I'm a little confused on your configuration.....

If the site column that you are using is a lookup (it presents you with choices and it is not a choie column), then it is not supported for ODATA filter purposes.

If the "E-mail" column is a single line of text, then your issue may be that there is a "-" in the name - this causes the internal column name to be a little funky.  I believe that the filter would look something like:

E%5Fx002d%5Fmail eq

To se the internal name (used by Flow in the ODATA filter), go to list settings and click on the email column.  Take a look at the URL.  The last part of the URL (after Field=) is the internal column name.

If you a referencing a Lookup via Dynamic properties, you should select the option that says "Value":

E-mail Value

In my case:

https://MyTenant/sites/flow/sub/_layouts/15/FldEdit.aspx?List=%7BD2C27955%2D7A42%2D4436%2D8E0D%2D47A04CC91D8A%7D&Field=E%5Fx002d%5Fmail

Scott

[trishmalloy]

Here is the internal field name, "https://staffcscc.sharepoint.com/teams/trish/_layouts/15/FldEdit.aspx?List=%7BF645D36F-E9AF-41CA-ADC...

Since there is no - I wonder what the issue is.  I just ran the flow again, it made it past the Get items state but is failing on the Apply to each stage, "ActionFailed. An action failed. No depended actins succeeded."

Update item failed with, "BadGateway. 

{

"error": {

"code": 502,

"source": "flow-apim-msmanaged-na-northcentralus-01.azure-apim.net",

"clientRequestId": "dff934e2-144d-4758-9e12-03a107bd5fb4",

"message": "BadGateway",

"innerError": {

"status": 500,

"message": "Item does not exist. It may have been deleted by another user.\r\nclientRequestId: dff934e2-144d-4758-9e12-03a107bd5fb4\r\nserviceRequestId: e8f1a39e-10ce-7000-a802-e8cda33619f8"

}

}

}

[trishmalloy]

The email field in the List of Clients field is populated, it's a required field.  I'm not sure why the error message states it doesn't exist.  ?

[ScottShearer]

Flow uses a columns internal name.  The URL that you posted ends with this:

Field=EMail%22%C2%A0

EMail%22%C2%A0 is the columns internal name.

So, try an ODATA filter that starts like this:

EMail%22%C2%A0 eq

Scott

[trishmalloy]

BadRequest

{

"status": 400,

"message": "The expression \"EMail%22%C2%A0 eq ''\" is not valid.\r\nclientRequestId: 524737e7-cf11-4206-9ee0-70656f0b0172\r\nserviceRequestId: 22f7a39e-40c4-7000-778f-0a591ff54089"

}

The Get items sections has my list, list name (List of Clients) and Filter Query is set to, "Email%22%C2%A0 eq 'Email ' -picked from Dyanamic Content.

[trishmalloy]

I just noticed something ...

Here's how the flow goes:

When an item is created in SITEA on THISLIST | Get items from SITEB on THATLIST, the Dynamic Content is bringing up THISLIST fields from SITEA, not fields from SITEB's THATLIST ...?  I'm guessing this is my actual problem.