Can Requested Data be Compared Against Product Available Data

GMichal

I have a situation where a form request for products populates a sheet but the products requested may not be available in that market so the request is an invalid one. It would be great if, after form submission, we could compare the new row (request) against a matrix sheet of available products. Has anyone done that before with a Smartsheet solution?

Here is a simple example of my situation:

An Arizona customer is requesting Product B and Product C but, according to our availability matrix, only Product A and B are available in AZ. I'd like to compare the customer request with the available products in that state to notify the customer that their request could not be completed. Is there a way to compare a request against a data matrix of available products?

Customer Requests

Product Availability Matrix

This is a simplified example as the actual number of states, territories, and countries could be in the hundreds at some point and the products in the availability matrix could grow to a hundred as well.

Stefan

Hi @GMichal,

with form logic you can show dropdown fields, when a certain condition is met. Checking a once dann new entry for availability in the selected region could be done with workflows ending with a message being sent automatically to the requestor.

Would it maybe be more elegant to show the requestor only products available in his/her region right away?

If you agree, how about using a 3rd party tool that allows to have dropdown menus in Smartsheet that update upon changes in a central management sheet? This would require 1 field logic rule per region in the request form. For hundreds of entries maybe very much, but you only need to create them once. What's in the dropdown would be defined in the management sheet.

Hope this helps

Stefan

GMichal

Hi Stefan:

Yes, these are possible solutions but I see that the weekly updates to all of the form logic to satisfy the 100's of ongoing products added and deleted as well as the weekly additions of new territories to be too time-consuming to maintain. That is why is was looking for a lookup solution from the form and intake Smartsheet to our master offerings Smartsheet matrix of Territories and Products which would be ever-updating without any human intervention.

Stefan

Hi @GMichal,

right, I do not have enough insight regarding how many regions and products and frequency of change to provide a good idea.

But as you are talking about 100's of products and many regions, please be aware of the limitations of Smartsheet:

https://help.smartsheet.com/articles/506775-system-requirements-for-using-smartsheet

Whatever solution with a formula looking up values in your matrix, if the matrix sheet grows to hundreds of columns and thousands of rows, you might run into a performance issue.

Hope this helps

Stefan

jmyzk_cloudsmart_jp

Hi @GMichal

I would add a helper column, "Available Products", at the Product Availability Matrix, as shown in the first image below. ("AB" for Az, for example.)

Then, at the Customer Requests sheet, you can get the Availability Product data with cross sheet references like:

JOIN(COLLECT({Product Availability Matrix Avai lable Products}, {Product Availability Matrix State}, [State Requested]@row)))

With the Availability Product data, you can use the CONTAINS function to see if the customer-requested products are not available, as shown in the second image, like:

=IF(CONTAINS([1st Product Requested]@row, <<Availability Product data formula starting JOIN>>),0,1)

You can create workflow automation to send an email alert to customers using Not Available checkboxes as the trigger.

The demo published dashboard's sheets are partially editable, so you can check how the formula works by changing the matrix. (Save the change and refresh to see the new availability.)

https://app.smartsheet.com/b/publish?EQBCT=9367dfc582434e78a55f61f4a8e7d4b0

GMichal

jmyzk_cloudsmart_jp, thanks for the response. It is using some formulas that are new to me so I'll have to study your solution and apply on a small scale test.