Is there a word or term for this? The other day someone asked me for help on a problem, "I've got an integration where I receive somewhere between 0 - 10 tax codes and need to figure out what [unique] tax group they all belong to."
To solve, we're going to use the Contoso DB. Here is a look at the TaxGroupData table:
Let's look at the CA group; it has three codes, HR_CAST, RP_CAST, and SP_CAST. The problem with doing a search on just these values, is that not only do I get the CA group back, but also CALA and CALA-USE, which have those codes within their [larger] sets:
The key to solve the problem is not just to match on the tax codes [attributes], but the count of them in a set. I say attributes because I consider that to be what it really is at the most abstract level, and have done a similar pattern for advanced product configurators.
To get the number of lines in a set at the line level, I join the table to a SQL expression (which could easily be a view in x++) on [the header] TaxGroup:
Now we know how many attributes or lines are in each set. The CA tax group has 3 lines, HR_CAST, RP_CAST, SP_CAST whereas the CALA group has 6: HR_CAST, HR_LACITY, RP_CAST, RP_LACITY, SP_CAST, SP_LACITY.
Now when I search on the three codes, I can see the three groups that set is in, but I can also tell that two of those have larger sets with additional attributes:
We only want the taxgroup that has a perfect circle Venn diagram with the supplied tax codes, so we need to also filter on count of tax codes:
This essentially gets us there. From here, there are many options on how where to take this depending on the context of the call or how it will be used. For demo ease, here is a the final query with a top 1:
That's it! This can all be reproduced in x++ in a number of ways. The best way will be up to you.
