You’re offline. This is a read only version of the page.
6
I’m trying to build a model driven app for my company to track service ticket, time and expenses.
I have several tables, but here are they tables I’m currently focusing on building out: Technician, Time, Service Ticket.
On the Technician table I have a column titled Technician (Tech’s Name) and a column titled Bill Rate (Currency Field). I have a table titled Holidays, which has a Holidays column that consists of dates that are holidays like: 1/2/2023, 2/20/2023, 5/29/2023, 6/19/2023, 7/4/2023, 9/4/2023, 10/9/2023, 11/10/2023, 11/23/2023 and 12/25/2023.
On the Time table, which is linked to the Service Ticket table via lookup, there is a Date column. How do I compare the date selected on the Time table to the dates on the Holidays table and if there is a match returned the selected Technician’s Bill Rate from the Technician table x 1.3 (effectively adding 30% to the Bill Rate).
I also need to do this if the date selected on the Time table is on a Saturday or Sunday.
Otherwise, I would like to just return the normal Bill Rate from the Technician’s table for the selected Technician.
Okay
At the end just multiply this variable with the Technician rate.
So here’s what I did:
Trigger: When a row is added modified or deleted
Change type: Added or Modified
Table Name: Times
Scope: Organization
Select Columns: Date
List Rows
Table Name: Technician
Filter Rows: hsc_Name eq Technician (Value)
Compose
Select an output from pervious steps: Bill Rate x 1.3
List Rows
Table Name: Holidays
Condition
Time Table Date is equal to Holidays
If yes… and this is where I get confused
So a powerautomate on the creation of the service ticket would be the easiest approach,
1. You can use day of the week expression to check on weekdays
Please check for more info on how to use it https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Power-Automate-Fundamentals-12-Usage-of-DayofWeek-in-Power/ba-p/1257521
2. You can use list rows to check if the date selected in your service desk ticket is on holiday.
https://learn.microsoft.com/en-us/power-automate/dataverse/list-rows
Easiest approach to construct your advanced find download fetchxml and use it in your List Rows.
https://learn.microsoft.com/en-us/power-automate/dataverse/list-rows
3. Based on 1&2 use a compose operation calculate the rate.
Hope this gives you an idea!
I don't need the user to see it. It's mainly for invoicing purposes when I bill the customer.
You need to use either JavaScript or Plugin to do that if you need this to be synchronous in the sense you need the user to see the calculation immediately, Alternatively you can use power automate to do the below logic:
1. On change of the date field , you need to check if it is a holiday by checking if the date exists in your configuration table or check if it is Saturday or Sunday by checking the day of the week.
2. Calculate the rate based on the above.
If you know which approach you want to follow Synchronous versus Asynchronous I can help you by trying to replicate sample easiest will be using powerautomate
#1
mmbr1606
22
Super User 2025 Season 1
#2
stampcoin
17
#3
ankit_singhal
11
Super User 2025 Season 1
Share
Report
All responses (
Answers (
