Categories From Part Descriptions

Kinetic ERP
1

I am cleaning up our hardware inventory. It is a bunch of small bins that hold tiny bags of leftover and stocked hardware items. I would like to apply a category to some of these items to help group them more intelligently on the shelves.

The part descriptions have words like nut, bolt, screw, pin, bearing, and spring. I want to define a list of the categories, and have a BAQ put each part in a based on what word is in the description. The group can just be a calculated field for now.

My problem is that some descriptions contain more than one of the category terms. For example: spring pins. I want these to be grouped with pins, not springs.

However, accounting for all the variable ways people put in part descriptions is too tedious. I would settle for grouping the part into the category that matches the first word in the description. So if the description is “spring pin” it can get grouped with springs, and “pin spring” can get grouped with pins. Its not ideal but its fine for now.

I was just about to post asking for help with this, but after writing it out I thought that GPT could write this expression. It came up with this tidy solution:

CASE
    WHEN Part.PartDescription LIKE 'nut%' THEN 'nut'
    WHEN Part.PartDescription LIKE 'bolt%' THEN 'bolt'
    WHEN Part.PartDescription LIKE 'screw%' THEN 'screw'
    WHEN Part.PartDescription LIKE 'pin%' THEN 'pin'
    WHEN Part.PartDescription LIKE 'bearing%' THEN 'bearing'
    WHEN Part.PartDescription LIKE 'spring%' THEN 'spring'
    ELSE 'uncategorized'
END

This worked well enough for what I needed. I can now expand the list, and categorize a bunch of my parts!

4 Likes
2

If you want to see multiples, you could do a column for each, and then put a % at the beginning of your search word. Then you would be able to see if it’s in more than one category. Might be helpful to sort out the easy ones first.

Make the fields bits (check boxes) then do a column for each.

CASE
    WHEN Part.PartDescription LIKE '%nut%' THEN 1
    ELSE 0
END

CASE
    WHEN Part.PartDescription LIKE '%bolt%' THEN 1
    ELSE 0
END

etc.

Many ways to skin a cat here, and unfortunately, they are all gross, lol. English is dumb.

5 Likes
3

Nice! One problem I noticed is that if I am looking for ‘ring%’ and the description is ‘o-ring’ it doesn’t get flagged because there are no characters after ring. :thinking:

4

It doesn’t find it because you have the % only after. So it has to start with ring. If you put it on both sides, then it’s a wild card before and after.

Edit, keep in mind that if you put the wild card on both side, the case statement that you have will short circuit to the first thing it finds.

so:
“spring pin lead screw” (yeah, it;s nonsense)
will have a category of “Screw”, because that was the first one in the list that it matched.

CASE
    WHEN Part.PartDescription LIKE '%nut%' THEN 'nut'
    WHEN Part.PartDescription LIKE '%bolt%' THEN 'bolt'
    WHEN Part.PartDescription LIKE '%screw%' THEN 'screw'
    WHEN Part.PartDescription LIKE '%pin%' THEN 'pin'
    WHEN Part.PartDescription LIKE '%bearing%' THEN 'bearing'
    WHEN Part.PartDescription LIKE '%spring%' THEN 'spring'
    ELSE 'uncategorized'
END
4 Likes
5

Whoops! That was easy!

6

So stop doing this.

Add attributes to the part or something easier to sort by.

2 Likes
7

Someday that may work, but for now I have to deal with tens of thousands of parts that are already in the system. Pulling categorical keywords from the part description is working nicely. :slight_smile:

2 Likes
8

We are tackling it now.

2 Likes
9

first AppStudio mod was cascading category combos

image
image676×615 21.1 KB

1 Like