Answered

Service Ticket Model Drive App Holiday and Weekend Bill Rate Based on Date Selected.

(0) Share

Report

Posted on by HSCNOrthAmerica

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Mira Ghaly 11,415 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @HSCNOrthAmerica

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

Was this reply helpful? Yes No
HSCNOrthAmerica 6 on at

Like
a
(0)

Report
Copy link

Link copied!

I don't need the user to see it. It's mainly for invoicing purposes when I bill the customer.

Was this reply helpful? Yes No
Mira Ghaly 11,415 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@HSCNOrthAmerica

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!

Was this reply helpful? Yes No
HSCNOrthAmerica 6 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Mira Ghaly 11,415 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@HSCNOrthAmerica

Okay

1. Initialise a variable for the the additional rate if Holiday or weekend at the very top and set it to zero. .

After list Holidays, add a condition step to check the length of List Holidays if greater than 0 then in the yes set the rate variable to the extra 1.3

Repeat with another condition to check the week day

At the end just multiply this variable with the Technician rate.

Was this reply helpful? Yes No