Our team for any upcoming competition put together a google sheet on what parts or equipment to order in order to build the robot that season. (eg. https://docs.google.com/spreadsheets/d/1Y6KkqBcIPi1s7Vgdvnxqt_XtH_NhLWy3qeTBCrPtxNs/edit#gid=0). We are wondering if you use a similar method to have the team put stuff they need on the list and the team and mentors evaluate if we actually need it or not. I was wondering what are some other ways teams have been putting together order lists.
Our team uses an ordering system which I created using a series of Google Forms and Sheets. It’s a little clunky and I don’t have much documentation on it.
Here is the workflow:
Students go to a specified URL to submit requests
For single items, they enter name, vendor, price, link, qty. needed, etc.
For bulk orders (more than 5 unique SKUs from the same vendor), they fill out an itemized Google Sheet (with a template we provided) and then upload that sheet to the form
Then, mentors receive a notification is our Slack #ordering channel (this is also where students can go to find relevant URLs if they forget them). The notification provides a link to the master spreadsheet.
The master sheet lists all of the individual orders submitted and imports all of the bulk items, as well. The sheet has spots for approval of items, denial of items, or a change in quantity (ex: we actually only need 4 of something but the student said 6). There is also a column for the order number or confirmation link. Students will receive an email automatically once the mentor has approved or altered an order they submitted.
Also - very finicky - when the mentor provides a confirmation number from certain retailers, a box on the spreadsheet will pull the carrier tracking information.
If there is enough demand, I will consider documenting stuff in more detail. In the mean time, I’m happy to answer questions about this. Hope this helps!
Hi,
This year, I have attempted to implement an order request form, similar to yours, with the addition of a column for an importance rating, and a column for a rationale as to why it needs to be bought. It works pretty well as a system, as the links and other information are provided, and you can judge pretty easily what is imperative and what is less so. My spreadsheet is not for robot materials; it is used for hand tools and consumables (i.e. duct tape, drill bits, deburring tools, etc). Its only been in place a couple of weeks, but has proved to be really helpful. This is also a great way for team members to be able to write down observations for things missing in the lab (i.e. "oh hey we don’t have any scissors in the lab. I’ll post a link of where to buy them rather than complain about it). It also helps keep everything in one place, and to be able to publicly keep a running tally of what commonly-used items are being purchased.
I obviously can’t show the board for an example, but AndyMark uses Trello for its purchasing process. We’re doing this at a bigger scale than an FRC team, but the concepts carry over nicely.
Each item someone wants bought, whether for a product or something internal like a tool, gets a card with this information and any prints or details needed:
Project:
Part Number Needed: (Y/N)
Development or Production:
Quantity:
Projected Cost:
Material Type:
Requested Supplier:
Requested Delivery:
Shipping Preference:
Special Ordering Instructions:
The card then moves through different lists as appropriate:
- Template
- Quote Needed
- Quote Response
- Hold for Engineering Action (usually, confirming a quoted something is the something we want approved or making tweaks for it)
- Need Approval by Director (for purchases)
- Approved for Purchase
- Purchased, Waiting for Delivery
- Received
- Returns
- Mothballed
- Denied/cancelled
At 1072 we have a custom purchase request system that is really easy to use. Includes presets for different vendors, places to add shipping and tax. The PR goes through two different levels of approval, first our leadership, then the mentors. After that, all the parts are ordered. We use the generated Bill of Materials from OnShape to track what had been ordered, what has arrived, and what has yet to be ordered. This system has worked great, and we really like it.https://gyazo.com/0533ee4d105f535e54053d607f13879e
A lot of the formality of Part ordering depends on how your team Finance System is controlled, and the source of funds.
If all (or some) your funds are held by a PTSA or ASB; they will have some particular pre-purchase authorization requirements, and specific documentation methods.
If your funds are held by a booster club, 4H, or other non-profit (501C3); they may authorize x dollars per project, with full receipts and a forms afterwards.
There are also limitations on how some funds are spent depending on source requirements. We have some grant funding limited to non-durable goods like registration, fasteners, or sheet metal (but no tools).
1987 started using a spreadsheet like you show. We sorted into tabs by vendor to simplify ordering on our coaches. We also added date ordered and date received columns so we could easily see what we got, what is still on its way, and what has yet to be ordered.
Spreadsheets like this are also super useful when making your cost accounting worksheet.
This is basically exactly the solution 449 uses. It has had…varying levels of success, though the problems have not really been due to the system itself.
I would love more info on this. This is our first year with fourth year member students and I’m starting to levy a heavier requirement for people to tell me what needs to be bought.
In particular, I’d like to know how y’all set up how to get a slack notification in a channel. And how the uploaded sheet gets added to the master list (is that just manually done, or automatic?)
Regarding the Slack notifications, we use Zappier which sends a custom message in response to a new row on the Google Sheet.
For the bulk Google Sheets, people use the following workflow:
Go to a specified URL
Prompted to make a copy of Google Sheets template
Fill out order form
Share doc as anyone with the link can view (important)
Paste the document URL into a specified cell (you’ll see why in a minute)
Open a Google form which will ask for the document URL - user will paste the same link into the form and submit it
The system does the following:
=ImportRange formula imports raw bulk data from the URL entered on the form
Parse sheet creates a hyperlink to the order in the format: <<VENDOR>> Order ($<<TOTAL>>) - <<SUBMITTER EMAIL>> @ <<TIME/DATE>>
Hyperlink is then listed on the front end page for approval. If the hyperlink is clicked, approver will see the bulk form
Once the status is updated on the master sheet, the bulk form will display the status using another =ImportRange formula (now, it is pulling from the master to the individual). This is why it is important to enter the URL on the current doc (it searches the bulk form for the imported URL and matches it to current status).
I hope this all makes sense - if not, let me know. I’ll also try and make some sample sheets soon but that may be awhile.
Thanks for the info on Zappier - I’ll check it out.
What’s “the system” is it another google sheet that goes and parses the data? Is it a python script somewhere? The quoted part still kind of left me confused.
Parse sheet creates a hyperlink to the order in the format: <<VENDOR>> Order ($<<TOTAL>>) - <<SUBMITTER EMAIL>> @ <<TIME/DATE>>
Hyperlink is then listed on the front end page for approval. If the hyperlink is clicked, approver will see the bulk form
Once the status is updated on the master sheet, the bulk form will display the status using another =ImportRange formula (now, it is pulling from the master to the individual). This is why it is important to enter the URL on the current doc (it searches the bulk form for the imported URL and matches it to current status).
Thanks for the info on Zappier - I’ll check it out.
What’s “the system” is it another google sheet that goes and parses the data? Is it a python script somewhere? The quoted part still kind of left me confused.[/quote]
By the system, I purely mean Google Sheets. When I say parse, it is not ~real~ parsing - the Google Sheet splits and combines columns to make it less wonky/more easily humanly readable. Check out these screenshots with descriptions for each step on Imgur.](https://imgur.com/a/mWkCm)
