Add a new value to an EnumList from a view using a different table

[StephenSaid]

I have two tables: Catalogs and Products. 

In the Catalogs table, I have a column named [products] of type EnumList with a base type Ref referencing the Products table.

I want to be able to add or remove a product to the Catalogs products column when I click on the a product listed in a view of Products.

While adding or removing products, I know which record in the Catalogs table I need to add the products too as it will be marked as checked out with the user's email. The field for this is [checkedOutBy].

Is this possible?

[TeeSee1]

In your other post you showed the scheme of your database.

Since your products table has a ref column to Catalogs, the system should already have created a column called [Related_Products] in the Catalog table, which is automatically updated when you specify a catalog in your product row.

So what is your business reason to have an additional ENUMLIST column which to me seems redundant.

[StephenSaid]

Hi @TeeSee1 

I updated the schema now and moved the RefList in the Products table rather than the Catalogs table. 

So in the product, I now have a list of catalogs that the product belongs to. As it was, I was not able to update the catalog table from the product table. This solved my issues.

As for the other post, I have resorted to listing the products in a Card view grouped by Categories.

As it stands, the issues I have are:

1. When a product belongs to more than one category, the category heading is a list of categories that the product belongs to. I prefer to have the product listed under each category it belongs to.

2. I have an expression in Format Rules to highlight products which are in a checked out catalog (currently being edited - adding and removing products from it). The expression is to expensive. I am trying to find a better solution for it.

IN(SELECT(Catalogs[catalogID], [checkedOutBy] = USEREMAIL()), [catalogsLink])

[TeeSee1]

1. Instead of capturing categories that a product belongs to as a ref LIST within the products table, I would create a many to many relations table with id (a VC with a combination of ref to prod and ref to category) , ref to prod, ref to category, columns that pull required data from the products table such as picture, price, desctiption...

This way you can create a view with a grouping by category

2. I wouldn't worry about it unless you are actually having performance issues.

[StephenSaid]

@TeeSee1 Thanks

1. I'm going to try and implement your suggestion. Will revert with outcome.

2. I do have performance issue. When I tap on a product, it get added to the catalog but the formatting takes 3-4 seconds to reflect the change. This is not a user friendly experience.

[TeeSee1]

Just to see if the format rule is the cause of your performance issue, disable it and see if the issues still persists if you haven't tested it already.

[StephenSaid]

@TeeSee1 

I've implemented your suggestion and it works great. Was a bit more complicated since I have another table (Items) which Products table is dependent on. This is the schema as it stands now. It now works as expected thanks to your suggestion.

The formatting is not delaying the updating of the DB. It simply takes an additional 3-4 seconds to format the column to give feedback to the user that the product was added to the catalogue. Not a good user experience! Not sure how I can fix it. 

[TeeSee1]

Good that it is working for you. I personally would not place the categoryID ref in the products table because that is redundant although you might create a vc to show that in some view.

---

@StephenSaid wrote:

takes an additional 3-4 seconds

---

What do you mean here? In addition to what?

[StephenSaid]

@TeeSee1 

You're right. I forgot to remove the categoryID from the schema. I actually do not have it.

The formatting takes some 3 seconds to sync the update to the DB and an additional 3 seconds to update the formatting.

Here's a link to how it looks in real time.

[TeeSee1]

 How about

[catalogLink].[checkedOutBy] = Useremail()

[StephenSaid]

@TeeSee1 

First of all, thank you for persisting in helping me out on this issue. I really appreciate it.

catalogsLink is is a coloumn of type EnumList with a base type of Ref: Catalogs in the Products table.

Now we have your suggested ProductsCategories table which is working really well as already told you above. This table contains several VC.

I might have set the catalogsLink virtual column incorrectly. As it is at the moment, it is a list of type text.

So to reference the [catalogLinks].[checkedOutBy] coloumn from the ProductsCategories table (or from the Product table), I have to first check which of the catalogLinks is actually checked out since a product can belong to several catalogs.

I was thinking about creating a new table (example CatalogsProducts) and do the same thing you suggested with the ProductsCategories but as it is it's practically working fine for me. Furthermore, I do not see it solving this issue anyway.

What do you think?

[TeeSee1]

I see what you mean. It takes time for changes in VCs to propergate.

I have a workaround which I tested and works but I do not like it very much. Just too much complication for something that may not be so business critical.

You can create a real field in Products and/or ProductsCatalog tables and set/unset them concurrently with the changes in the Catalogs field using grouped actions.

[StephenSaid]

@TeeSee1 

Hi there. I just posted a demo showcasing the problem I'm facing here.

I appreciate if you find the time to look into it.

Thank you.