Hierarchial selection with "exclusion"

[Jouko_Manninen]

Hi!

The title is over simplication for what I try to accomplish but I guess thats what it is by heart.
I’ve been fighting with this way too long and finally decided that maybe I could find the answer from here. So here it goes: (Sorry for the long post, I try to break this down to segments)

CONTEXT:
We rehouse photography lenses to cinema use. So this means that client sends us the lens and we disassemble and reassemble it with new parts and send it back (Rehouse it). So we have a part stock that needs tracking.

The incoming lens dictates that what kind of parts is needed for that particular lens. (And there is many parts for one lens). Lens can be Nikon, Canon, Leica etc.

TARGET:
Idea is, that when an order comes in, we create a puchase order with a “status” selection for that lens. When the “status” is changed something like “work in progress” it should “reserve” all the parts from the stock, that are related to that lens/ Order. THIS WORKS NOW

LOGIC:
I have been able to “break down” the parts in “hierarchial categories”, which means that depending on the selections made in the purchase order, it selects and reserves only those parts that fits to the selected category and sub categories. And the hierarchy goes like this:

Category: Generic, RS95, RS110
When part is “tagged” as “generic”, it means, all purchase orders reserves that part when in “work in progress” status. Example of such part is PL Mount, no matter what lens, all of them is going to have a PL Mount.

Next in the “hierarchy” is Category: RS95 or RS110. These parts goes either the RS95 or RS110 houses, regardless of the size etc. Screws might be this kind of parts.

Then there is size, XS,S,M,L,XL Most of the parts lands in these categories.

And lastly, we have some “dedicated” parts that fits only to certain brand, like Nikon or Canon etc.
(And these are the issue at the moment)

So,
When a new part is added to the stock, user defines, what kind of part it is, it can be generic, RS95 or RS110 or RS110+S and if it’s dedicated part, it’s RS110+S+Nikon for example.
(category + Size + brand)

So I’ve managed to build the purchase order so that the “selection” chain goes always like this:
Category + Size + Brand = (RS95+S+Nikon) And the selection logic then run downs the parts that meets the criteria and gets “reserved” if the PO status is “work in progress”

If part is generic, get selected (In all cases)
If Category = Category ,get selected
If Category + Size equals ,get selected
if Category + Size + Brand equals, get selected

PROBLEM:
Now everything else works fine except with “dedicated” parts. They get selected properly but the
problem is, that when there is a “dedicated part” it means that there’s also a duplicate of that part that is “not dedicated”.
And the “not dedicated part” should be then “unselected” when “dedicated part” gets selected. (If the Brand matches.)

EXAMPLE:
From part point of view, we have part named “front ring” that belongs to RS95+S. No matter
what brand, this always fits to this category. One day we notice that it doesn’t work with Nikon,
we need to modify it for Nikon. So we create a duplicate of it and create “dedicated” part that is modified only for Nikon, and the “original” “front ring” works with all the rest of the brands.

So the logic should then be that we have two parts named “front ring” and “front ring Nikon”, front ring goes to all RS95 S Brand (except Nikon) And the “dedicated part” Only to RS95 S Nikon.

Now I can get all the necessary parts selected, but
what I can’t get to work is how to exclude the “original” part when the Dedicated part should is selected.

MY SOLUTION AT THE MOMENT:
I’m Doing the selection with Action that get’s triggered when ever a modification is made in the purchase orders.

I have managed to identify, if there’s a duplicated parts in the table (This indicates that then there’s also dedicated part existing for that part)

I can then select all the duplicates that fits to that category, (So it should select all the dedicated and non dedicated parts) and what I’m hoping to accomplish with this selection is to check: What is the brand of the purchase order? Nikon, does anywhere in these selections have a brand Nikon? If yes, don’t select part. (Because this would mean that this current part have a dedicated part for Nikon)

Now this seems to work only two ways, either:
IF [Brand] = [_THISROW].[Brand]
This doesn’t work because non dedicated part doesn’t have any brands defined because it should work all brands by default, so [Brand] is “Blank” for the non dedicated part
Or
IF [Brand] = [Brand]
which seems to check if there’s anywhere in the column a brand named Nikon and so, will always fail if there’s any dedicated part for that brand whether it was the same part or not.

What I’m asking, is that is it possible to have “within these current selections” check if the referenced purhace order Brand equals Brand. And then it either is True or False. And don’t get selected.

SUMMARY:
What could be solution for this? Somehow I should be able to exclude a “non dedicated” parts from brands that have “dedicated part”.

[Jouko_Manninen]

SUM(
SELECT(
Order[Reserved] , OR (
[Mount]=[_THISROW].[Part name] ,
[Iris]=[_THISROW].[Part name] ,
[Generic]=[_THISROW].[Category] ,
IF (
AND([Category] = [_THISROW].[Category] ,
[_THISROW].[Category] = [_THISROW].[Lens ID]) ,
[Category] = [_THISROW].[Category] ,
IF (
AND([Lens ID] = [_THISROW].[Lens ID] ,
[_THISROW].[Lens ID] = [_THISROW].[Dedicated ID]) ,
IF ([_THISROW].[Not Duplicate?] = FALSE ,
IF (AND ((LEFT ([Part ID] , 12)) = [_THISROW].[Dedicated Ref] ,
[Brand] = [_THISROW].[Brand]),
FALSE,
[Lens ID] = [_THISROW].[Lens ID]
) ,
[Lens ID] = [_THISROW].[Lens ID]
) ,
[Dedicated ID] = [_THISROW].[Dedicated ID]
)
)
)

                   )

) * [Quantity per lens]

[Steve]

Your expression reformatted for legibility:

SUM(
  SELECT(
    Order[Reserved],
    OR(
      [Mount] = [_THISROW].[Part name],
      [Iris] = [_THISROW].[Part name],
      [Generic] = [_THISROW].[Category],
      IF(
        AND(
          [Category] = [_THISROW].[Category],
          [_THISROW].[Category] = [_THISROW].[Lens ID]
        ),
        [Category] = [_THISROW].[Category],
        IF(
          AND(
            [Lens ID] = [_THISROW].[Lens ID],
            [_THISROW].[Lens ID] = [_THISROW].[Dedicated ID]
          ),
          IF(
            [_THISROW].[Not Duplicate?] = FALSE,
            IF(
              AND(
                LEFT([Part ID], 12) = [_THISROW].[Dedicated Ref],
                [Brand] = [_THISROW].[Brand]
              ),
              FALSE,
              [Lens ID] = [_THISROW].[Lens ID]
            ),
            [Lens ID] = [_THISROW].[Lens ID]
          ),
          [Dedicated ID] = [_THISROW].[Dedicated ID]
        )
      )
    )
  )
) * [Quantity per lens]

[MultiTech]

Hello @Jouko_Manninen

Assuming I’ve gotten things correctly,

Jouko_Manninen:

What I’m asking, is that is it possible to have “within these current selections” check if the referenced purhace order Brand equals Brand. And then it either is True or False. And don’t get selected.

I think what you’re needing is help with your valid if formula.

Taking your hierarchy into consideration: if I had a form that allowed people to select options for each criteria, my SELECT() formula would look like this:

SELECT(Table[Column], AND(
  [Criteria1] = [_thisrow].[Criteria_1_Selector], 
  if(isnotblank([[_thisrow].[Criteria_2_Selector]), 
      [Criteria2] = [_thisrow].[Criteria_2_Selector], 
  true), 
  if(isnotblank([[_thisrow].[Criteria_3_Selector]), 
      [Criteria3] = [_thisrow].[Criteria_3_Selector], 
  true)
))

What the IF statements do with the TRUE statement at the end, is basically tell the system to only consider that branch if the IF() statement is true - if it’s false, then the system should essentially ignore it.

[Jouko_Manninen]

Hi!

Thank you for the input!

What I understood of the structure of your example it looks pretty much the same to what I have, the order and criteria is just a bit different. (I use CONCATENATE([Category],[Size],[Brand]) as matching criteria. So by combining names I get to “climb up” from the more “generic” parts towards more “exclusive” parts. And everything works as long as we don’t need to deal with the “dedicated” parts.

The issue lies with the “dedicated” parts because the “dedicated” part is basicly same as the “non dedicated” part, but it only should go to that one particular lens/brand, but the “non dedicated” should go to all lenses BUT the one that the dedicated part occupies. And at the moment I don’t have a way to tell the “non dedicated” part that don’t get selected for that lens because it uses “dedicated part” instead.

The current structure selects both, “dedicated” and “non dedicated” part.

Your solution might very well be a correct one because it might be a matter of correct ordering of the selections, but I need to test how to get that implemented to my needs. But I have been suffling the orders and criterias and I’m feeling that I maybe I need one more “loop” of “criteria selectors” to tackle the issue, or the issue infact is with the duplication of the part and need to take another approach.

EXAMPLE:
By default, all parts are “unique” so the selection branch is kinda straight forward (And I believe it’s the same you suggested?)

For example: There’s just one RS110_XS_Compendiumring (And by this criteria, Any lens brands that are built in RS110_XS housing, uses that part.) (Category_Size_Partname)

And we have many lens brands that goes in to that housing:
Canon (RS110_XS_Canon)
Leica (RS110_XS_Leica)
Nikon (RS110_XS_Nikon)
Kowa (RS110_XS_Kowa)

No problem here, everything works and correct parts get selected no matter which brand you choose since the Category and size matches.

But if we now notice that the current RS110_XS_Compendiumring doesn’t work with Nikon, we need to modify it, and this means, we create a “dedicated part” of it, and this would be then: RS110_XS_Compendiumring_Nikon. (Category_Size_Partname_Brand)

Now when we use the same selection logic, the original RS110_XS_Compendiumring gets still selected
along with the RS110_XS_Compendiumring_Nikon when RS110_XS_Nikon is selected.

RS110_XS_Compendiumring_Nikon won’t get selected by any other brands because it has the brand name included in its name that we can use to “include” or “exclude” from the other brands.

I don’t know how to tell to the RS110_XS_Compendiumring part to NOT to be selected by the RS110_XS_Nikon if a dedicated part for that current part exist because there is no indicator for it to know if dedicated part exist, but also know if should be included or excluded by particular brand.
(I have found a way to tell if there’s a dedicated part for the current part by searching any duplicates from the table, but haven’t found a way how to utilize it and even bigger issue is to identify the brand what to exclude)

Only solution at the top of my head for the current model to work would be that when ever a dedicated part is created, it should be created for all brands, and the original part should be “nulled” or something. But that’s not how it really works, physically we still would have only two different RS110_XS_Compendiumrings in stock. One for RS110_XS_Nikon and one for alle the rest of RS110_XS brands.

Jouko

[MultiTech]

You should most definitely include an additional tag for each item - dedicated or not.

Then with this flag, you can easily filter out the one’s you need by including another selection field to add to your criteria.

SELECT(Table[Column], AND(
  [Item_Dedication] = [_thisrow].[Dedication_Selector],
  [Criteria1] = [_thisrow].[Criteria_1_Selector], 
  if(isnotblank([[_thisrow].[Criteria_2_Selector]), 
      [Criteria2] = [_thisrow].[Criteria_2_Selector], 
  true), 
  if(isnotblank([[_thisrow].[Criteria_3_Selector]), 
      [Criteria3] = [_thisrow].[Criteria_3_Selector], 
  true)
))

[Jouko_Manninen]

No luck still. I think the issue is not with the selection criterias (Or it is but the selection criteria tree won’t work like this with the dedicated parts).

Because my current structure for selection is: (Select this part if this is true)
[Category]
[Category] + [Size]
[Category] + [Size] + [Brand]

The brand is never defined for the part, because by default, all [Brand] gets always selected (Or to be exact, because it’s not defined, the selection stays at [Category] + [Size] level, and so Ignoring the brand and all the brands get’s selected.

But when dedicated part is created, there we define to which brand it belongs to and so gets selected
by the third criteria [Brand]. [Category] + [Size] + [Brand]

Dedicated part is a duplicate of the original part and should be selected instead when [Brand] criteria is met:
If Dedicated part, get selected by [Category] + [Size] + [Nikon] ([Nikon] being the [Brand] it’s dedicated to)
The non dedicated part should then be selected by [Category] + [Size] + NOT([Nikon])
(But all the other brands should select the non dedicated part)

I get the dedicated part selection to work but I can’t get the exlusion for the original part to work.
All I managed to do is that either it gets always selected or never selected when there’s a dedicated part present.

So the issue is that:
1.
The part should know that Dedicated part of that current part exist. (I managed to solve this by
using question, is this part dedicated part, and if yes, have a slice of the table to show only dedicated parts in there) And now the original part can check from that table that does this current part have dedicated part created.
2.
To what brand this part is dedicated to (And use that information to not get selected by that brand)
This I haven’t got to work.
3.
is the selector what brand. If Nikon, don’t get selected, if Leica, get selected.

And I think it is because either all brands gets selected or one brand gets selected, but there’s no
inbetween. (As a selection option)

EXAMPLE:
UlkorunkoRS95S [Part name]+[Category]+[Size] (should be selected by all brands)

Dedicated part is created from this part:
UlkorunkoRS95SNikon [Part name]+[Category]+[Size]+[Brand] (Should be selected by Nikon only)

And
UlkorunkoRS95S (Should now be excluded from Nikon) But since the [Brand] is not defined here,
I don’t have a way to NOT select this when Nikon is the brand.

The Brand is always defined by the selector, so it’s always a Nikon or Leica etc.

Jouko

[Jouko_Manninen]

My current progress: And with this I get the dedicated parts to work, the decision tree for the non dedicated should be defined on the “FALSE” part.

SUM(
 SELECT(
  Order[Reserved] , OR ( 
    [Mount]=[_THISROW].[Part name] , 
    [Iris]=[_THISROW].[Part name] ,  
    [Generic]=[_THISROW].[Category] ,
    IF (
         AND([Category] = [_THISROW].[Category] ,  
             (isblank[_THISROW].[Size]=TRUE)) , 
         [Category] = [_THISROW].[Category] ,
         IF (
             AND([Category] = [_THISROW].[Category] , 
                 [Size] = [_THISROW].[Size] ,
                 [Dedicated ID] = [_THISROW].[Dedicated ID]) , 
             [Dedicated ID] = [_THISROW].[Dedicated ID] ,
             FALSE                                      
             )                                                                 
         )   
         )                         
       )
) * [Quantity per lens]

FALSE should be the branch to decide if or not to select the non dedicated.

For second branch to find out if dedicated part exists
I have been trying this: (And seems to work)

IF(
AND([Housing ID] = [_THISROW].[Housing ID] , "[Housing ID] = [Category]+[Size]"
[_THISROW].[Part Category ID] = Dedicated parts[Part Category ID]) , "Check If part exist in dedicated table"
 TRUE, 
 ([Housing ID] = [_THISROW].[Housing ID]) "If this part isn't shown in the dedicated table, use this selection criteria"
)

And if this part has a dedicated part in the table, check if the brand is the same as the dedicated part
if it is, don’t get selected

IF(
AND([Housing ID] = [_THISROW].[Housing ID] , "[Category]+[Size]"
([Lens] = [_THISROW].[Lens]) , "[Lens] = Brand"
[Dedicated ID] = [_THISROW].[Dedicated ID], "Dedicated ID = [Category]+[Size]+[Brand] so select the dedicated part"
TRUE "(FALSE)Select the non dedicated part"
)

Like this it always selects both dedicated and non dedicated part.

[Jouko_Manninen]

I guess the issue is the selection from the Order table. At the moment If I create a dedicated part for Nikon:
UlkorunkoRS95SNikon [Part name]+[Category]+[Size]+[Brand] (Should be selected by Nikon only)
This can get selected by Nikon since it is now “referrable” by the [Dedicated ID] Column in the Order table (Dedicated id is CONCATENATE From [Category]+[Size]+[Brand])

So I guess, when dedicated part is created, it should create all the possible variations of that part to be able to select or not to select some parts. So, at the moment, there is no [Dedicated ID] To select
UlkorunkoRS95SLeica for example if I don’t create a dedicated part for it separately.

SO QUESTION:
Is there a way to have all the possible Brands collected inside one column (Enum List) (I have a table where all the brands are collected) and then, if the Selection [Brand] is any of the brands in the [Enum list] Select it? (I think it starts to complain that text can’t be compared to enum list or something?)

[MultiTech]

Jouko_Manninen:

text can’t be compared to enum list

Instead of doing this:

"Text" = [EnumList]

do it like this:

IN("Text", [Enumlist])

[Jouko_Manninen]

So I would need to type alle the possible brands in the “Text”?

Could this work so that the “text” is taken from the name inside current row [Brand] column?
Something like:
IN("Text", [list]) = IN("Text", [_THISROW].[Enumlist])

And since the [Brand] column would contain a name of “Nikon”
And Enumun list have “Leica”, “Nikon”, “Canon” , “Kowa” ,“etc”, can it pick the one name from the list or is it trying to match the text to the whole list?

But thank you, will try to play around with this logic!

Jouko

[Jouko_Manninen · Accepted solution]

Actually your first suggestions were correct! Just needed some more criterias with true/false results AND it was also my misunderstanding that the TRUE/FALSE result should be directed to certain column selection instead it being enough that the result is justTrue or False.

So my solution when dedicated part is created is to tag it:[Dedicated part?] = TRUE and use this as slice for a table of dedicated parts.

Then all parts check:
Does this current part have a dedicated part (Checks if it can find itself from dedicated part table)

If it can’t, then we know that it doens’t have dedicated duplicate and can get selected no matter what.

If it finds itself, then it needs to check, is the current lens that is picking this part, have dedicated parts?

Order table lens, check if there is a dedicated part for it (True False)
Stock table part use the same formula to tag itself (True or False)

And if this current part is not dedicated part but the lens that is picking has dedicated part, then don’t get selected (since it is not that dedicated part)

And the other then selects only those dedicated parts but they actually get selected on earlier stage so this doesn’t have any result.

Need to test this around more but seems promising.

SUM(
 SELECT(
  Order[Reserved] , OR ( 
    [Mount]=[_THISROW].[Part name] , 
    [Iris]=[_THISROW].[Part name] ,  
    [Generic]=[_THISROW].[Category] ,
     IF (
      AND([Category] = [_THISROW].[Category] ,  
         (isblank[_THISROW].[Size]=TRUE)) , 
      [Category] = [_THISROW].[Category] ,
      IF (
       AND([Category] = [_THISROW].[Category] , 
                [Size] = [_THISROW].[Size] ,
                OR(ISBLANK([_THISROW].[Dedicated Part?]) ,
                  [_THISROW].[Dedicated Part?]=FALSE)) , 
        IF(
         [_THISROW].[Not Duplicate?] ,
         TRUE, 
         IF(
          [No dedicated parts?] = [_THISROW].[No Dedicated lens?] ,
          TRUE,
          FALSE
             )
            ) ,
            [Dedicated ID] = [_THISROW].[Dedicated ID]
           )                                                                 
          )   
         )                         
       )
) * [Quantity per lens]