AnsweredAssumed Answered

How to find line item duplicates on specific scenarios, in different tables, and remove them according to the type?

Question asked by user28177 on Nov 20, 2018
Latest reply on Dec 15, 2018 by user28177

Hello All,

 

I’m trying to create a system for a Car Wash. They break each service in different tasks, and each task has a different price and time estimate. So the sum of the tasks is the Total of a Service and also how long it will take to complete the service.

 

A Service is a group of Tasks.

 

So I have a Table called Quotes, where the person enter the quote for a client’s car.

A Quote will have Services, and Services will have Tasks.

 

One QUOTE can have many SERVICES, and one SERVICE can have many TASKS.

 

Since SERVICES and TASKS are a many to many relationship, I’ve created a table to connect them called SERVICES_TASKS.

 

QUOTE – SERVICES -> SERVICES_TASKS <- TASKS

 

Now, in order to make it easier for the person who is going to work on the bid, I’ve created some “Templates” of  Services on a different table relationship.

 

SERVICES_TEMPLATE -> SERV_TEMPLATE_TASKS <- TASKS

 

TASKS Table have two table-occurrences, one in SERVICES_TASKS and another in TEMPLATE_TASKS.

 

So in the QUOTE layout I have a portal SERVICES that shows all the services related to that QUOTE. I’ve also have a portal inside a popover with a “X” relationship that lists all the values of SERVICES_TEMPLATE. When a user click at one of the SERVICES_TEMPLATE Records, the script copy the values of the SERV_TEMPLATE_TASKS and create new Values on SERVICE_TASKS.

 

All of the above works.

 

There are a few services that cannot co-exist in the same Quote.

 

Let’s say I have two Services Templates, one called Simple Car Wash, and another called Complete Car Wash

 

Simple Car Wash -SERVICE

Tasks:

1-Rinse the car

2-Apply Soap

3-Remove Soap

4- Dry

 

Complete Car Wash -SERVICE

Tasks:

1-Rinse the car

2-Apply Soap

3-Remove Soap

4- Dry

5- Vacuum inside

6- Apply Wax

7- Remove Wax

 

The Quote cannot have both a Simple Car Wash Service and a Complete Car Wash Service (a Quote is per car, so if a client with more than one car at the same time will get more than one quote), because I would be billing my client twice for 4 of the tasks. So my first idea was to replace the existing service with the other. So I created a table called CONFLICTED_SERVICES, related to SERVICES_TEMPLATE, with a field with a popup menu with service ID value list, and the script looks there to check if the service one is trying to add to the Quote conflicts with another, and if yes, asks to replace.

 

So far so good.

 

Now the problem I need help with.

 

Lets say the services complement each other, but there is still some overlap of tasks.

 

Simple Car Wash -SERVICE

Tasks:

1-Rinse the car

2-Apply Soap

3-Remove Soap

4- Dry

 

Wax the Car – SERVICE

Tasks:

1-Rinse the car

2-Apply Wax

3-Remove Wax

 

When I add both services to a Quote, I don’t want to have Task “1-Rinse the car” duplicated, since I only need to Rinse the car once, if the client ordered both the wash and the wax.

 

Since there are some services that will need to keep duplicated tasks, I created another table called COMPLEMENTARY_SERVICES, the same way I’ve created the CONFLICTED_SERVICES Table to let the system know which services it should look for duplicates and which services it will allow duplicated tasks.

 

My questions are:

 

1. What would be the best approach to look for tasks duplicates, in the services that are complementary, so the client gets a better deal when ordering multiple services?

 

2. Will this approach be a better way to handle conflicted services as well? Meaning, conflicted services are basically services with overlapping tasks, so if I figure out a way to handle the overlapping tasks, I can use one approach for both scenarios.

 

3. Any other tips or advice on the method I'm using for this solution?

 

Thank you!!

 

Best,

 

Paulo

Outcomes