Backend System v2.0

Employing new technologies and more importantly, revamping the backend!

Backend Excel Architecture

Give it a try here: https://tiaptiapwithsoph.cococart.co

Converting Address Input into Area Categories

How it was done previously

Within the google form, I would manually get users to self-identify where they stay in Singapore. Do they stay in the east? the west? central?

How it is currently done

STEP 1: Extract only the Postal code from the string
=RIGHT(M2,8)

STEP 2: Remove all letters
=IFERROR(SUBSTITUTE(N2,CHAR(CODE(LEFT(N2))),""),"")

STEP 3: Remove all letters again
=IFERROR(SUBSTITUTE(O2,CHAR(CODE(LEFT(O2))),""),"")

STEP 4: Remove any spacing
=TRIM(P2)

STEP 5: Get the first 2 numbers
=LEFT(Q2,2)

STEP 6: To determine the Area Category
=IFERROR(VLOOKUP(R2,location,2,FALSE),"")

First 2 numbers to map to Postal District Indicating Area Category

Mapping 3 Major Indexes

This is a system that is being used by my parents hence it needs to be rather intuitive. What can usually be solved by a pivot table, needs to be explicitly done in raw formulas so that the general interaction on the excel sheet is limited to picking dates from a dropdown. Pivot table filtering has proven to be challenging and a high learning curve.

There are 3 major functions occurring throughout this excel sheet.

Extracting Specific Orders based on Unique ID's

In short, how do I display orders that this person ordered?

The basis of all of these indexes relies on one very simple mechanic

Index	Description
1	Counts the number of rows there are based on the CSV rows	=IF(A3<>"",ROWS($A$3:A3),"")
2	If user ID corresponds with the dropdown you have selected, it would highlight which rows have it. Example: User ID TEST01 exists in rows 15 to 19. Therefore, index 2 will take note of which rows have these values.	=IF(A3<>"",IF(A3='4️⃣ Print Order Form'!$C$9,V3,""),"")
3	This just consolidates all of the row numbers that correspond with the dropdown value to the topmost portion of the table.	=IFERROR(SMALL($W$3:$W$500,V3),"")

Now that I have identified the relevant rows, I just need to display the specific information that I want. In this particular case, I want to display the product items, unique ID has placed and ordered.

I defined a range for the products and quantities in the original CSV data file. The important thing to note is that the column you want to extract the information out of should be the first column of your defined name range. In this case, I have defined the name to be productquantities

=IFERROR(INDEX(productandquantities,$X3,COLUMNS($AA$2:AA2)),"")

Extracting products ordered on the day of production as well as customers that ordered on a particular day

These 2 other concepts follow the same execution as seen above. This is just to ensure that I get different types of views for different purposes each time I extract my CSV.

Why does such a small business require such a complex background?

As a business, you are constantly interfacing with many different types of stakeholders who require different forms of raw data. Example: Drivers for delivery, Kitchen to prep for production, Overall quantities over several days to account for logistics + lead time before production date.

Managing Production Quantities

As you can see, my parents just need to go in to select a dropdown (created via data validation)

The product and qty columns are a result of querying the results from the index. As there can be duplicates in these columns, how I approached consolidating quantities are as such:

=SUMIFS($J$2:$J$500,$I$2:$I$500,R24)

Printing out an Order Form

The new Tiaptiapwithsoph Invoice

Here you can see the culmination of all the indexes I have used. You also notice that there are only 2 dropdowns that my mother needs to control and print out the respective order forms.

And all of this? In excel. Periodt. 🥳