Support
Anonymous
Anonymous
 · Posted in Support

Filtering Month and Frequency of Maintenance task

Hi everyone,

I'm currently working on a maintenance form that uses SharePoint data components. In my form, I have a "Machine Number" field. Based on the selected machine number, it filters out the "Month" field, and based on the selected month, it filters out the "Frequency/Tasks" field. The selected frequency/task then shows the instructions for maintenance on the next page. The months range from January to December, and the frequencies/tasks include Weekly, Fortnightly, Monthly, Bi-Monthly, Quarterly, Bi-Annual, and Annual.

In my month component, I'm using the following expression in the filter query:

show= item['Asset Number'] === data.assetNumber['Asset Number'] && item.Frequency === 'YES'

Here, "YES" is a text value in the frequency column in Excel, indicating that the task is planned for maintenance.

I was advised that the month component should only allow or force the user to select the months where maintenance is not yet done. For example, if the current month is May and planned maintenance is due for January, February, March, May, and July, and the user has completed maintenance for January and February but not for March, May, and July, the form should not show January and February. It should force the user to select March, May, and July, which are due for maintenance.

Additionally, if January has both Weekly and Annual planned maintenance, and the user has completed only the Weekly maintenance, the form should still show January so the user can select the Annual frequency/task, while hiding the Weekly frequency/task since it's already done.

I'm working on this query but haven't found a solution yet. Is there any workaround or guidance you can provide? Any suggestions would be greatly appreciated.

Thanks!

Best reply by Erin Dwyer

Hi Anonymous

The way I would approach this would be to add a new column to your excel file called "Status" which would be populated with "complete" or "pending" depending on weather the user has completed the maintenance task. You could then simply update your filter to only show items where the status is still pending e.g.

show = item['Asset Number'] === data.assetNumber['Asset Number'] 
      && item.Frequency === 'YES' 
      && item.['Status'] === 'pending'

You can use Microsoft Power Automate to update the "Status" column of your excel spreadsheet to "Complete" when ever a form is submitted for that specific maintenance task.

View original
2 replies