Calculated field

%3CLINGO-SUB%20id%3D%22lingo-sub-1453215%22%20slang%3D%22en-US%22%3ECalculated%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1453215%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20looking%20to%20create%20a%20calculated%20field%20to%20achieve%20the%20following..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20date%20field%26nbsp%3B%20called%26nbsp%3B%20ExpiryDate%26nbsp%3B%20%26nbsp%3Band%20I%20have%20a%20calculated%20Datefield%26nbsp%3B%20called%20CalcExpiryDate%20with%20the%20following%20syntax%20which%20works%20great%26nbsp%3B%3DItemDate%2BTermDays%20this%20returns%20a%20date%20and%20time%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20items%20will%20have%20an%20ExpiryDate%20and%20some%20won't%2C%20all%20will%20have%20a%20calculated%20date.%26nbsp%3B%20I%20want%20to%20create%20the%20following%20result%3B%20if%20ExpiryDate%20is%20blank%20then%20use%20the%20above%20calculation%2C%20if%20not%20use%20the%20ExpiryDate..%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWas%20thinking%20could%20do%20this%20in%201%20of%202%20ways..%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EExtend%20the%20syntax%20in%20the%20existing%20calculated%20field%20to%20do%20the%20calculation%20in%20that%20field%20if%20possible%3C%2FLI%3E%3CLI%3ECreate%20an%20extra%20field%20called%20ActualExpiryDate%20which%20checks%20the%20other%202%20and%20gives%20the%20result..%3C%2FLI%3E%3C%2FOL%3E%3CP%3ECan't%20seem%20to%20hit%20on%20the%20right%20syntax..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1453215%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1458563%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458563%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20know%20if%20you%20can%20do%20this%20with%20a%20calculated%20column%2C%20but%20I%20would%20do%20it%20with%20a%20Power%20Apps%20customised%20form%20and%20do%20the%20logic%20there%20instead%20of%20using%20the%20default%20SharePoint%20form.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20SharePoint%20list%20and%20you%20can%20see%20that%20it%20achieves%20what%20you%20want.%20The%20ItemDate%2C%20ExpiryDate%20and%20CalcExpiryDate%20are%20all%20Date%2FTime%20columns.%20The%20TermsDays%20column%20is%20a%20number%20column%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-List.png%22%20style%3D%22width%3A%20797px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198337iFF686EA9504722F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SP-List.png%22%20alt%3D%220-SP-List.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20customised%20Power%20Apps%20form%20(from%20the%20Power%20Apps%20menu%20above%20your%20list).%20I%20have%20renamed%20the%20datacards%2C%20labels%20and%20data%20fields%20so%20that%20they%20are%20understandable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20Default%20Date%20of%20the%20dataCalcExpiryDate%20you%20need%20to%20enter%20the%20following%20expression%3A%3CBR%20%2F%3E%3CSTRONG%3EIf(IsBlank(dataExpiryDate.SelectedDate)%2C%20DateAdd(dataItemDate.SelectedDate%2CValue(dataTermDays.Text))%2C%20dataExpiryDate.SelectedDate)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3Ewhich%20means%20that%20if%20the%20dataExpiryDate%20is%20blank%20then%20add%20the%20value%20in%20the%20dataTermDays%20field%20to%20the%20dataItemDate%20selected%20date%2C%20otherwise%20(i.e%20it's%20not%20blank)%20then%20use%20the%20selected%20date%20in%20the%20dataExpiryDate%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-CustomisedFormPA.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198338i0F228CFDBBCB650F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221-CustomisedFormPA.png%22%20alt%3D%221-CustomisedFormPA.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Form.png%22%20style%3D%22width%3A%20900px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198350i4F5EBE5FF5AA561D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222-Form.png%22%20alt%3D%222-Form.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EOne%20gotcha%3C%2FU%3E%3A%20due%20to%20the%20way%20Power%20Apps%20handles%20null%20values%20in%20a%20datepicker%20(or%20rather%20it%20doesn't%20due%20to%20a%20programming%20oversight%20apparently)%20you%20need%20to%20turn%20on%20the%20experimental%20feature%20shown%20below.%20If%20you%20don't%20do%20this%20then%20if%20you%20delete%20the%20date%20in%20the%20dateExpiryDate%20field%20and%20save%20it%20won't%20delete%20it%20and%20will%20keep%20the%20previous%20value%20saved%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%2299-ExperimentalFeatures.png%22%20style%3D%22width%3A%20707px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198336i7AA4FF3B3BBDFA91%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%2299-ExperimentalFeatures.png%22%20alt%3D%2299-ExperimentalFeatures.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20you%20can%20see%20it%20in%20action%20on%20the%20attached%20video.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

I'm looking to create a calculated field to achieve the following..

 

I have a date field  called  ExpiryDate   and I have a calculated Datefield  called CalcExpiryDate with the following syntax which works great =ItemDate+TermDays this returns a date and time value.

 

Some items will have an ExpiryDate and some won't, all will have a calculated date.  I want to create the following result; if ExpiryDate is blank then use the above calculation, if not use the ExpiryDate.. 

 

Was thinking could do this in 1 of 2 ways..   

  1. Extend the syntax in the existing calculated field to do the calculation in that field if possible
  2. Create an extra field called ActualExpiryDate which checks the other 2 and gives the result..

Can't seem to hit on the right syntax..

 

Any ideas?

1 Reply

I don't know if you can do this with a calculated column, but I would do it with a Power Apps customised form and do the logic there instead of using the default SharePoint form.

 

This is the SharePoint list and you can see that it achieves what you want. The ItemDate, ExpiryDate and CalcExpiryDate are all Date/Time columns. The TermsDays column is a number column:


0-SP-List.png

 

This is the customised Power Apps form (from the Power Apps menu above your list). I have renamed the datacards, labels and data fields so that they are understandable.

 

In the Default Date of the dataCalcExpiryDate you need to enter the following expression:
If(IsBlank(dataExpiryDate.SelectedDate), DateAdd(dataItemDate.SelectedDate,Value(dataTermDays.Text)), dataExpiryDate.SelectedDate)

which means that if the dataExpiryDate is blank then add the value in the dataTermDays field to the dataItemDate selected date, otherwise (i.e it's not blank) then use the selected date in the dataExpiryDate field.

 

1-CustomisedFormPA.png

 

2-Form.png

 

One gotcha: due to the way Power Apps handles null values in a datepicker (or rather it doesn't due to a programming oversight apparently) you need to turn on the experimental feature shown below. If you don't do this then if you delete the date in the dateExpiryDate field and save it won't delete it and will keep the previous value saved in the list.

 

99-ExperimentalFeatures.png

 

And you can see it in action on the attached video.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User