Over the weekend I was working on a query for a drop down list for items on a PO. The list had 2 sections:

1. Job Estimated Items
2. Vendor Items

Each section would show up in different circumstances with different items, but usually they show up together. Each section had a line that was a header line using ————Dashes And Title to separate the two sections.

The header lines were dummy lines that loaded the data on the rest of the line (the other columns) with dashes. If the user selects this row it cancels the change and ignores it. If they select an item, it updates the appropriate fields (like unit, price, description, etc.) with the values for that line definition.

The request for changing this list was to:

1. Add a new section that loaded other lines that had been created for the job on any other PO.
2. If it was a repeat of the Job Estimated Items list, it should not show in the new section. Only different items should show.
3. This new de-duplication rule was also set to apply to the existing Vendor Items.

My approach ended up being to create a single hidden field that appended all the visible fields in the queries together. Then I built a list of all the de-duplication keys in the Job Estimated Items section and used a “NOT IN ()” clause to make sure the same de-duplication keys were removed from the other sections. This worked pretty well.

I built a function to create the de-duplication key SQL because I had to make sure they used the exact same data in the exact same order. But each section was coming from a different table, so the fields were different. This made the process much easier and less prone to errors. It also allowed me to make sure nulls were converted the same way for all the keys.

I ended up being happy with the results. I was concerned about speed, but I’m doing all the queries on indexed fields and there is not too much data in the result set.