I need help with a formula, I need it to return a blank cell.

Cindy Bowker

Okay I will try to explain better. When ordering scaffolding there are several different parts that need to be ordered in different amounts. For instance they will order frames, cross braces, planks, pigtails, out riggers, guardrails.

I would like to be able to have them order everything off of one form. I think in order to do this I need a field for each of these categories. I don't believe I can just use one field like I have on the current sheet.

My thought is that we need a separate sheet for scaffold that has a column for each of the different categories that need to be ordered from.

I hope this makes sense.

Cindy

Paul Newcome

So for frames there are multiple selections, cross braces have multiple selections, etc etc? Multiple selections from multiple categories?

Cindy Bowker

yes that is a possibility

Paul Newcome

How many different categories do you have, and what is the maximum number of items possible in any one category?

Cindy Bowker

I would think that we would have 5 separate categories and no more than 4 or 5 choices per category. In our accessory category there may be 2 or 3 items that they need to order per form.

We would probably have the following categories: Frames, Planks, Cross Braces, Guard Rails, Accessories.

I would think that for the first 4 categories they might only have one choice but multiple pieces each.

Paul Newcome

Ok. So here is what I am thinking. It is only 2 of probably quite a few different solutions. You may need different column names or need to add extra columns. The details such as those can be rather variable, but at the end of the day I personally would probably end up using one of the the two solutions outlined below.

Please let me know what you think or if you have any questions. I would be more than happy to explain further or try to think up a different solution if neither of these will fit your needs.

Create a blank (for now) dashboard. We are going to use the URL for this dashboard here shortly in one of the solutions.

Set your sheet up so that there is one column for each category, another column for the number ordered, and another column to be used as a "Summary" type of column (this comes into play later).

So each of your Category columns will be single select dropdowns that list off each of the possible options for that particular category.

Your number ordered column will be a simple text number.

The Summary/Category column will be text/number as well.

We now have a sheet that looks like this:

Next we will move to building out the forms.

First form will be for Frames, so we will name the form "Frames", make the Category column "Hidden" and enter a Default Value of "Frame". Then we will leave the "Frames" dropdown field, remove the other categories, and leave the Count field.

Next we move to the form settings. After a form is submitted, we want to either "Send the user to link" or "Reload the same form for another entry". I will explain the differences shortly and let you decide. Go ahead and skip to the bottom of this post to see the differences before making your selection if you would like. If you choose to send the user to a link, enter the URL for the blank dashboard we initially created.

Save that form, then go ahead and duplicate it. Open it up and make a few tweaks:

Change title to "Planks".

Change Default Value in Category field to "Plank".

Remove Frames Field.

Put Planks field in it's place.

Save that form, duplicate it, then tweak it again for each of the remaining categories.

Finally we will move back to the blank dashboard. This is where the option for what to do after form submission makes a difference.

Option 1:

"Send the user to a link" and enter the dashboard link.

For this, you would use a Shortcut Widget and include links to each of the forms.

The user goes to the dashboard, selects the form they want, submits it, and then is taken straight back to the dashboard where they can select another form.

This option is useful if you want to be able to display additional information on the dashboard.

Option 2:

"Reload the same form for another entry"

For this one, you would use a series of Web URL Widgets to display each of the forms on the dashboard.

The user goes to the dashboard, and can fill out and submit the form of their choice right there. Since it reloads after submission, there would be no need to refresh anything, and all of your forms are right there in one easy to use location.

This option is useful if you do not need to show any additional information on the dashboard. Since all forms are in a central location and can be filled out right there, it makes submissions very quick.

Cindy Bowker

Thanks for the feed back Paul,

I have a few things on my desk I need to clear up today, but will get to this shortly when I can dedicate some time to working just on this.

Thanks for all your help so far, I will let you know what I come up with.

Cindy

Paul Newcome

Sure thing. If you are interested, I can do a couple of "mock-ups" and share them to you so you can visualize exactly what I am referring to.

Cindy Bowker

That would be amazing if you have the time. I should be able to get to some work on this today I hope. Dealing with another issue at the moment.

Thanks for all the advice and help

Cindy

Paul Newcome

I have created a (currently empty) workspace to start working in. If you would like to share your email address, I will share the workspace to you.

I will keep an eye on my emails so that I can share the workspace as soon as possible so that you can remove your email from this thread if you prefer.

Cindy Bowker

cbowker@glastech.ca

I appreciate all the help that you are providing.

Cindy

Paul Newcome

I have shared you to the workspace as Admin. That way you can take a look around and see if anything will work for you. It also allows you to create new items in the workspace if you wanted to provide other examples towards a more appropriate solution.

Cindy Bowker

Thanks Paul,

I got the workspace. I will look at it when I get a chance hopefully this afternoon.

Cindy

Paul Newcome

No worries and no rush. 🙂