nested IntervalMatch? Is it even possible?

[#1]

Hi, Basically I have 3 ranges of organisation structure levels for each user:

UserJoe

Org1 = From AAAA To CCCC

Org2 = From FFFF To KKKK

Org3 = From XXXX To ZZZZ

UserMary

Org1 = From BBBB To CCCC

Org2 = From LLLL To OOOO

Org3 = From XXXX To ZZZZ

Now each record has:

Record     Org1     Org2     Org3

1000          B          F          Y

1001          C          G          Z

1002          A          K          X

I need your help in finding a way to link the Users to the Records where only they have the permission, ie, the record falls in their organisation structure.

Any ideas please? I thought of the IntervalMatch and then realised I might need to nest the 3 levels together but I dont know the syntax for it and cannot find it online 😕

Thanks

Melody

[hic]

If you interpret the ranges using radix 36 (see http://en.wikipedia.org/wiki/Base_36), you can use intervalmatch. However, you need several intervalmatches joined. The attached script should do the trick.

One complication is how to interpret a single letter. I have in my solution made all "numbers" 4 characters long, i.e. I have appended several zeros after the single letter, thus converting 'F' to 'F000'.

HIC

[#3]

Thanks a million, Henric!!

That really gave us a massive leg up

However we still have one unresolved issue I did not outline correctly before ... A record should only be correct if the org levels IN ORDER OF 1 - 2 - 3 are satisfied.

So if both 1001 and 1002 satisfy level 1, but only 1001 satisfies level 2, then only 1001 should show up in my data set for that user 😕

Therefore I need to create something on the lines of this algorithm:

if Org1 IntervalMatch returns True then

     if Org2 IntervalMatch returns True then

          if Org3 IntervalMatch returns True then

               DISPLAY CLAIM

I realise that what we are trying to achieve is complicated and we really appreciate your help

Thanks

Melody

PS. I attached the Sample Databse we are trying to use

[hic · Accepted solution]

Not sure this is the most optimal solution, but I think it works.

HIC

[#5]

Thanks so much Henric ... you have been wonderful and helped us so much!

This works fine for us and shall be adopting it

Thanks again!

Melody

[#6]

On the side, I am also trying to now join the THIRD table (PolicyGroup), but somehow as soon as I add the table everything goes haywire.

I have now spent the last 7 hours trying to find a way to join the UserName and Policy_Group in this table successfully without disturbing the already perfect logic there, but with no success.

The table is in the Sample Db I posted earlier

I would really appreciate your help!

Thanks

Melody