SOLVED

Creating a calculated column with data from another column in the same list

Copper Contributor

Total newb here.

 

List1 - 'Customer Details' a simple list of customer names and contact details.

List 2 - 'Build Details' uses a lookup col. to get the 'Customer Name' from List 1 and populates an 'Address' field with the relevant data from List 1 and stores additional data associated with that customer such as 'Build Start Date' and build specifications.

List 3 - 'Ordering' Uses a lookup col. to get Customer Name and Address fields from List 1 and data from list 2 associated with the relevant Customer Name and stores additional data associated with the ordering process.

 

I have created a column in List 3 called 'Start Date Lookup' that is a lookup and is linked to 'List 2'  'Start Date'.

 

In list 3, when I then create a calculated column called 'Start Date Display' and use the following formula:

 

=[Start Date Lookup].[Start Date Display]

 

I get a syntax error.

 

What I am doing worng??

 

 

Thankyou.

 

 

 

 

4 Replies

@Andy_Downs what calculation are you trying to do on the Start Date Lookup column? You get the error because a calculated column cannot refer to itself and the formula with a dot in it is not correct. Examples of common formulas can be found here.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

@Rob_Elliott Thanks for replying.

 

I'm trying to create a calculated column that reports 'true' when the current date is two weeks before the the 'build start the date'  so I can then use that 'true' to trigger a flow that sends notification mails.

 

 

best response confirmed by Andy_Downs (Copper Contributor)
Solution

@Andy_Downs forgive me for saying this but they way you want to do it is not the best approach, not least because the calculated column wouldn't update automatically anyway every day because it's just a static value. You should do it all in the flow.

 

So in this example we want to send a single email when any of the items are 14 days ahead, i.e 29 April. So that will be just Guadeloupe and Curacao.

 

0-SP.png

 

In your flow the trigger is a recurrence schedule. Set it to 1 day and in the advanced options set your time zone and the time you want the flow to run every day.

 

1a-Flow.png

 

 Next, add a Compose action, and click in the Inputs field. From the Expression tab of the dynamic content box enter the expression formatDateTime(addDays(utcNow(), 14),'yyyy-MM-dd') which calculate the date 14 days ahead of today:

 

1b-Flow.png

 

 

Next, add a get items action and select your site and list. Open the advanced options and in the Filter Query field type the column name (in my case Arrival) eq (which stands for equal to) then add 2 single apostrophes '' and inside them select the outputs from the 14 days ahead compose action.

 

 

2-Flow.png

 

Next, add a Select action and select value from the get items section of the dynamic content box. For the Map fields you need to enter a text value in the left column, and in the right column select the information you want to display which in this case is the Title field and the outputs of the 14 days ahead compose action.

 

Follow that with a Create HTML action and set the columns field to Automatic.

 

3-Flow.png

 

The default HTML table doesn't have any styling attached to it, so to make the table look better add another Compose action, paste in the CSS from the spoiler below and then at the very bottom select the outputs of the create html action.

 

Spoiler
<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 50%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>

 

4-Flow.png

 

Finally, add the send an email action, enter any text you want in the email and select the outputs of the formatting compose above.

 

5-Flow.png

 

This is the resulting email.

 

6-Email.png

 

If you don't want the flow to send an empty table if there is nothing 14 days ahead there are ways to deal with that, but hopefully the above will get you going for now.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

 

 

Thanks Rob, thats briliant. That will teach me to listen to co-pilot ;)
1 best response

Accepted Solutions
best response confirmed by Andy_Downs (Copper Contributor)
Solution

@Andy_Downs forgive me for saying this but they way you want to do it is not the best approach, not least because the calculated column wouldn't update automatically anyway every day because it's just a static value. You should do it all in the flow.

 

So in this example we want to send a single email when any of the items are 14 days ahead, i.e 29 April. So that will be just Guadeloupe and Curacao.

 

0-SP.png

 

In your flow the trigger is a recurrence schedule. Set it to 1 day and in the advanced options set your time zone and the time you want the flow to run every day.

 

1a-Flow.png

 

 Next, add a Compose action, and click in the Inputs field. From the Expression tab of the dynamic content box enter the expression formatDateTime(addDays(utcNow(), 14),'yyyy-MM-dd') which calculate the date 14 days ahead of today:

 

1b-Flow.png

 

 

Next, add a get items action and select your site and list. Open the advanced options and in the Filter Query field type the column name (in my case Arrival) eq (which stands for equal to) then add 2 single apostrophes '' and inside them select the outputs from the 14 days ahead compose action.

 

 

2-Flow.png

 

Next, add a Select action and select value from the get items section of the dynamic content box. For the Map fields you need to enter a text value in the left column, and in the right column select the information you want to display which in this case is the Title field and the outputs of the 14 days ahead compose action.

 

Follow that with a Create HTML action and set the columns field to Automatic.

 

3-Flow.png

 

The default HTML table doesn't have any styling attached to it, so to make the table look better add another Compose action, paste in the CSS from the spoiler below and then at the very bottom select the outputs of the create html action.

 

Spoiler
<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 50%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>

 

4-Flow.png

 

Finally, add the send an email action, enter any text you want in the email and select the outputs of the formatting compose above.

 

5-Flow.png

 

This is the resulting email.

 

6-Email.png

 

If you don't want the flow to send an empty table if there is nothing 14 days ahead there are ways to deal with that, but hopefully the above will get you going for now.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

 

 

View solution in original post