INTERACTIVE ORDER FORM

[COGICPENNY]

I started a non-profit to help those in low-income, minority and underserved communities. One of my projects is Project F.E.E.D. (Friends Ensuring Every Dinner) that addresses food-insecurity. I was recently awarded a grant from my State to provide 90,000 meals throughout NJ to those food-insecure and to fight hunger. I do not have the funds to purchase an ERP system, and need to create an ordering system. I have partnered (awarded) other non-profits throughout NJ in order to expand the reach of this program to feed more individuals and families than I could on my own. Each partner (site) would place orders that would go to a partnering restaurant to make meals and provide to the sites.

The form that I've tried to create would have some user inputs, vlookups for menu items, organization sites, and restaurant partners to place orders. It will also track the available meal balance they can order from their original meal awards. And finally, it will allow them to click a button to "PLACE ORDER" (brings up email) and "CREATE NEW ORDER" (creates new worksheet to begin the process again).

I'm not sure if this type of functionality even exists, but I know that the SMARTEST PEOPLE who would know is right here in this forum. You've helped me out of tough situations before, and I'm looking for that same help again -- YOU ROCK AND THANK YOU IN ADVANCE.

In the first tab "INSTRUCTIONS" I provided step-by-step tasks that outline how the functionality of the form would operate. In the tab "ORDER FORM" I've already created the Master Order form with sample VLOOKUPS for the data inputs. The file link is: https://1drv.ms/x/s!AsyIxS27y-3ThJI-RAq ... w?e=y4Jpl8

We launch this program this week and I'm hoping to train our sites on this form within the next 1-3 days.

PROJECT F.E.E.D. ORDER FORM - TASK SCREENSHOT.JPG

PROJECT F.E.E.D. RESTUARANT ORDER FORM.JPG

Thanks,
Penny

[HansV]

That's a tall order (sorry about the pun)

Will all users have Outlook (the desktop version)?

[COGICPENNY]

Hi Hans! I'm assuming so, but most people today use laptops (with Outlook). Hope that answers the question. Penny

[HansV]

Here is an unfinished version. Please enter one of your own email addresses for the restaurant to test.

I made the cells that need to be filled out stand out more. This is aesthetically less pleasing, but I found it confusing otherwise. You're free to change that of course.

Warning: this version need more work. For example, it uses 1 as sequence number.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsm

[COGICPENNY]

Thank you Hans. Will test now. Question, can you please clarify what you mean by "please enter one of your own email for restaurant to test"? What cell or tab are you referring to input the data? It should send the order to the restaurant email (APPROVED RESTAURANTS tab , email cell) with a copy back to me: projectfeed@powerchangeslives.com

[HansV]

In your sample workbook, no email address has been entered for Jake's BBQ and Grill in H2 of the APPROVED RESTAURANTS sheet.
If there is no email address, the code won't generate a message.
In the test phase, you don't want to send emails to this restaurant, so don't enter Jake's real email address in H2, but your own.

[COGICPENNY]

Gotcha. Yes I forgot to add that to this upload, but I did remember to add it to my sheet. My team will test it shortly.

[COGICPENNY]

Hi Hans

First, a round of applause!! what an AMAZING JOB you did on this file! Wow, just wow!

My team and I tested the file and it was mostly formatting issues, but there was one main issue all listed below. We also included a new "wish item" if possible, if not no worries:

Order No. (F2) should be in format "9734444444-09142021-1" which is phone number (no parenthesis, no hyphens), then "-" then "mmddyyyy" then "-1"
F3: 09/14/2021 (mm/dd/yyyy)
F6: 09/14/2021 (mm/dd/yyyy)
F10: I+Phone Number+ Delivery Date -1 so it would look like "I-9734444444-09142021-1"
F11: equals F6 (formatting) mm/dd/yyyy
B17, B2, A25-A30, F3, F4, F6, F7, F8 has a thick border around it, please remove borders and/or use the same formatting as the cells around it so that you can't tell this is an input field (we will lock the cells on the sheet so that they can only enter data on the unlocked cells).

TEST TO PLACE ORDERS: Success

TEST FOR NEW ORDERS: Failed
Reason: Award Balance (H17) didn't subtract from previous order ending balance (H17). Each newly created order's cell H17 should reflect the previous order's ending balance less the current (new) order balance (I31)

SPECIAL REQUEST:
Is it possible for the "PLACE ORDER" to export as a .pdf (Adobe Document) containing the current order worksheet (not entire workbook, as it's currently doing) versus an Excel worksheet? If not, it's okay. We just wanted something that has a copy of the finalized data that can't be changed.

Thanks so much,
Penny

[HansV]

Here is a new version. The email attachment will now be a PDF file.
I adjusted the formatting.

There are a few things I don't know how to do:
- How to calculate the order number and invoice number
- How to handle the balance.
We cannot do that when the user clicks NEW ORDER, because the organization is not known at that point.

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsm

[COGICPENNY]

Hi Hans, Again thanks for everything. In response to your questions:

- How to calculate the order number and invoice number
Order Number: (Cell F2) is the organization's phone"-"today's date-number sequence and should be in format "9734444444-09142021-1" which is phone number (no parenthesis, no hyphens), then "-" then "mmddyyyy" then "-1"
Invoice Number: (Cell F10): I+Phone Number+ Delivery Date -1 so it would look like "I-9734444444-09142021-1"

- How to handle the balance.
We cannot do that when the user clicks NEW ORDER, because the organization is not known at that point.
So we planned to give this form to each individual organization, so their ORGANIZATION information would then be already entered versus a vlookup. If we do it this way, you will now know the organization's information. Will this method resolve the problem now? The beginning balance will be the same because it is the SAME organization and won't change unless it's a completely different "form/document" that's entered for the next organization.

Hope this makes sense. Sorry for the delay, your message was stuck in my junk mail -- but it's NOT junk at all :)

[HansV]

I will take a look later today, I am away from home at the moment.

[HansV]

Here is a new version:

PROJECT FEED ORDERING SYSTEM - REVISED (TEST) 09102021.xlsm

[COGICPENNY]

Hans, we are 99.9% there with the main order form. The only thing I saw was that the Order date should be in MM/DD/YYYY format (it was in "Thursday, September 16, 2021" format).

The "CREATE NEW FORM " unfortunately failed. Here's the error code. (See pic).

Question, since we'll be updating APPROVED RESTAURANTS each day, is there a way to refresh this data somehow? Meaning we're going to send this form out to each organization by pre-populating the ORGANIZATION info part. So their form will always be "their form". Is this type of form supported in Google sheets? Thinking that way we can always update the restaurant list in real time. Your thoughts on how to do this?

Penny

[HansV]

Are you planning to use this on Google Sheets? I'm afraid that the buttons won't work - VBA is not supported in the browser.

You didn't attach a picture.

[COGICPENNY]

Hi Hans,

1. Google stated that "You can convert macros in Microsoft Excel spreadsheets to Google Sheets by re-creating them using Google Apps Script. Apps Script powers macros in Sheets, just like Microsoft Visual Basic for Applications does for Excel. your saved macro. You can also run your macro by using its keyboard shortcut."

2. Attached is the error code.

MicrosoftTeams-image (13).png

3. Is it possible to add another email address to the automated one you created:

• Automatically send to the restaurant's email address: (F20)
  Automatically send to Project FEED's email address: projectfeed@powerchangeslives.com
  Automatically send the Accounts Payable: ap@powerchangeslives.com

Automatically send to the Organization's email address: (F15)

4. NEW FORM UPDATES

• ** NEW** Cell F15: Has the ORGANIZATION email address. Figured we might need this since we are requesting that the "PLACE ORDER" function to create an email. It needs to be configured for the data input for Cell F15 as well.
  **NEW** Cell F20: Has the RESTAURANT email address. Figured we might need this since we are requesting that the "PLACE ORDER" function to create an email. It needs to be configured for the data input for Cell F20 as well.

Your humbled friend,
Penny

[HansV]

1. You'd have to rewrite the code from scratch in Google Apps Script. I cannot help you with that.

2. Would you be so kind to click the Debug button on the error message?
This will activate the Visual Basic Editor and highlight the line that caused the error in yellow.
Please copy that line and paste it into a reply.
You can then stop code execution by selecting Run > Reset in the Visual Basic Editor.

3. and 4. I'll take a look at those.

[COGICPENNY]

Okay, we'll keep it off of Google forms then and go with what you have.

Amount = Worksheets("ORG LIST").Range("A:A").Find(What:=Worksheets("ORDER FORM TEMPLATE").Range("B17").Value, LookAt:=xlWhole).Offset(0, 7).Value

[HansV]

Thanks, I'll take a look.

[HansV]

3. and 4. I thought the order had to be sent to the restaurant's email address, not the organization's email address. Which is correct?

[COGICPENNY]

Both so that everyone has a copy of the order. The organization for their records to track their orders placed, and the restaurant to see incoming orders.

Hope that clarifies things.

Penny