SKU/ID Assignment Process for Combos

[davidAtayde]

Hi everyone!

I'm working on a process for assigning SKUs/IDs. The app allows users to create combos, each containing one or more products. The idea is that if a new combo contains the same products in the same quantity as an existing combo, it should be assigned the same SKU as the existing combo. Otherwise, a new SKU should be generated.

The tables are as follows:

Combo: Contains the combos.

Combo_details: Contains the details of each combo, i.e., the quantity of each product in a combo.

Combo_sku: Records the SKU for each combo. This is done in a separate table to maintain a historical record of all generated SKUs, including those that belonged to a combo that has been deleted.

Product: Products.

• I've created a bot to register SKUs for each combo.

In the Combo table, I created a VC called "Matching?" to determine if a combo has one or more combos with the same product content. I use the value of this column as a condition for the process of registering the combo and its SKU in the Combo_sku table. If "Matching?" is false, then the combo will be registered with a new SKU. If it's true, the combo will be registered with an existing SKU.

The problem is when I want to assign the existing SKU. I've tried different formulas, but none have been successful.

 App link 

Do you have any ideas on how to solve this? 
Thanks in advance for your help!

Best regards,

David

[AlexM]

Pretty complex.
So if I got it right:

You have Combos and Products  and a bridge table Combo Details which has a ref column for each of the other tables.

Then in Combos you have a VC for matching, which is intended to check for related identical Combo Details. 
I can see it starts with COUNT(). Are you sure this one is working?
Shouldn't it be a LIST()? And then checked to have the same Combo Details? 

Moving forward, an SKU can have multiple Combos but your relation seems to be the other way around where multiple SKU belong to the same Combo. If I got it right so far, then Combos should have a ref column to SKU.

For later use in the Bot, in Combos add:
VC for the matching Combo
VC for the SKU of any matching Combos [MatchingSKU]

Pass this point, in the Bot when you are checking for matching, on the "Yes" branch, you would set the value of [Sku_ID] in the table Combos  to [MatchingSKU]

I am pretty sure I got it right, but I appologise if I didn't 🙂

[davidAtayde]

Hi AlexM,

In the VC "Matching?" I use the following expression:

COUNT([_THISROW].[Items]) = COUNT(INTERSECT([_THISROW].[Items],SPLIT(SELECT(Combo[Items],[COMBO_ID]<>[_THISROW].[COMBO_ID]),",")))

To search for a match in terms of items (product and quantity) between combos. It only indicates if there is a match, but not between which combos.

The item column in the Combo_details table uses the following expression:

CONCATENATE([Product].[Description],":",[Qty])

The items column in the Combo and Combo_sku tables uses the following expression:

SORT(SELECT(Combo_details[Item],[Combo_ID]=[_THISROW].[COMBO_ID]),TRUE)

I hope this helps you better understand where I'm at with this.

[AlexM]

I copied your app and went my way with it but it works!
Hopefully nothing I have changed was made for an alternative purpose 🙂

https://youtu.be/jAAfwfOwfqU

How did you share the app so I can share mine back here?

[davidAtayde]

Great AlexM!

To share the app, do the following:

Go to Manage > Collaborate & Publish > Team Work > enable the "Make this a public sample?" option. Now you can share the app link from the share menu.

Thanks again for your help!

[AlexM · Accepted solution]

App Sample:  ComboKit 

[davidAtayde]

Hello AlexM,

Thank you for sharing the app. I've tested it, but I'm a bit confused.

When creating combo #8, whose first match is with combo #4, it generates a different SKU_ID. I was expecting the SKU_ID to match, as in the case of combos #2 and #3.

I see that even though the bot's condition is met, the process still registers a new SKU.

Is this the correct behavior, or am I missing something?
Thanks in advance for your support!

[AlexM]

@davidAtayde 
OK, so now it works. Can't put my finger exactly where it went wrong but it had something to do with the virtual column, execution order and sync time.

I replaced the VC for combo matching with physical columns in Combo and now it works.  Check it again.

Flow:
In the calculated columns we check for matching combos during product addition.
If there is any, on creation, the SKU has an Initial Value formula = to matching SKU.

If there aren't any matching combos, then the combo is created with an empty matching combo column and it triggers the bot to create and assign a new SKU to the new created combo.

If there's something that doesn't make sense, reach out.

I have tested it a good few minutes ago and the results are consistent and stayed that way 😄

[davidAtayde]

Hi AlexM!

Thank you so much for your help! I was able to successfully implement your solution in my app. I really appreciate your dedication in working through this with me.

Kind regards.