How would I create an inventory table for several products that can each product can be sold 52 times and will take out of the inventory and put back upon cancellation

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have a sales table for a time share operation - we have a number of products or housing available to purchase on a weekly basis - so 1 product can be sold 52 times for each week of the year. They currently are doing it manually on a spreadsheet but its too big now - so they want it so when they use a pull down menu selecting a particular product it will give them the next product available in that year or provide a link with a calendar so they can see what is available. They also want if the client cancels the contract that they product will go back into the inventory.

I am not sure how to start this? Any suggestions
Photo of Roxanne

Roxanne

  • 142 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Ricardo Tsai

Ricardo Tsai

  • 40 Points
Hello Roxanne,

My approach would be:


Create 2 tables. 

Table 1 is Product. Table 2 is Operation.

Table 1 with 3 fields named: (Text) Product Name, (Number) Initial Quantity, (Formula Number) Current Quantity.

Table 2 with 2 fields: (Number) Quantity Sold, (Text Multiple Choice or drop down field) named "Type of Operation" that holds values like: "Sold", "Canceled", "Returned". whatever you like.

Create a relation ship of 1 Table 1 record to Many Table 2 records.

In Table 1, create Summary fields in the relationship where you can bring Summary of records in Table 2 according to some specific criterias like sold, returned.. etc.. That summary is the amount of records, of that product that are sold or returned. With that information, you can code the Table 1 "Current Quantity" to add/substract the initial amount with the summary amounts and you can get the real amount of inventory.


Keep in mind to update Table 2 operation records of when a product becomes sold to canceled.

Hope that approach helps.

Ric
Photo of Neal

Neal

  • 70 Points
I have created spreadsheet prototype for the TimeShare Application.

  1. Seq in the image specifies time sequence in which actions are performed in TimeShare application. This description is for Seq=1.  Assumption : TimeShare can be done with minimum 1 day.  Home1 is booked from 12/1/16 to 12/5/16 and as it is available  - its status is set to booked.
  2. This description is for Seq = 2. Customer calls and asks for availability of Home1 for 12/15/16 to 12/20/16. Booking person enters the data. BookIt link is available for the record. Clicking of BookIt link can result into either successful booking or can result into "Not Available". The customer tells booking person that he/she will call later regarding booking. So the data stays as found in Seq=2.
  3. This description is for Seq = 3. Another customer calls and would like to TimeShare Home1 from 12/2/16 to 12/4/16. Booking person enters the data. record status is "CheckAvailability" and BookIt link is available for record with Seq=3.
  4. This description is for Seq = 4. Booking person clicks BookIt link for timeshare from 12/2/16 to 12/4/16. It results into status "Not Available". It has BookIt link available which can be used to book from 12/2/16 to 12/4/16 at a later date if Home1 is available at later time because of potential cancel-operation for Home1.
  5. This description is for Seq = 5. The person who called for Home1 in Seq=3 calls again to book Home1 from  12/15/16 to 12/20/16. Booking person clicks BookIt link for record with Seq=3. It results into "booked" because Home1 is  available during that time. Link for "Cancel" is available. This "Cancel" link can be used by booking person to cancel the timeshare of Home1 from 12/15/16 to 12/20/16.
  6. This description is for Seq = 6.  The person who reserved Home1 from 12/15/16 to 12/20/16 calls and requests cancel. Booking person clicks "Cancel" link for record with Seq=5 and cancels it. Now status  becomes "Canceled" and BookIt link is available.

Please let me know if there are any questions.

Thanks,

Neal


NealPatil @ gmx.com
Photo of Neal

Neal

  • 70 Points
I have created spreadsheet prototype for the TimeShare Application.

  1. Seq in the image specifies time sequence in which actions are performed in TimeShare application. This description is for Seq=1.  Assumption : TimeShare can be done with minimum 1 day.  Home1 is booked from 12/1/16 to 12/5/16 and as it is available  - its status is set to booked.
  2. This description is for Seq = 2. Customer calls and asks for availability of Home1 for 12/15/16 to 12/20/16. Booking person enters the data. BookIt link is available for the record. Clicking of BookIt link can result into either successful booking or can result into "Not Available". The customer tells booking person that he/she will call later regarding booking. So the data stays as found in Seq=2.
  3. This description is for Seq = 3. Another customer calls and would like to TimeShare Home1 from 12/2/16 to 12/4/16. Booking person enters the data. record status is "CheckAvailability" and BookIt link is available for record with Seq=3.
  4. This description is for Seq = 4. Booking person clicks BookIt link for timeshare from 12/2/16 to 12/4/16. It results into status "Not Available". It has BookIt link available which can be used to book from 12/2/16 to 12/4/16 at a later date if Home1 is available at later time because of potential cancel-operation for Home1.
  5. This description is for Seq = 5. The person who called for Home1 in Seq=3 calls again to book Home1 from  12/15/16 to 12/20/16. Booking person clicks BookIt link for record with Seq=3. It results into "booked" because Home1 is  available during that time. Link for "Cancel" is available. This "Cancel" link can be used by booking person to cancel the timeshare of Home1 from 12/15/16 to 12/20/16.
  6. This description is for Seq = 6.  The person who reserved Home1 from 12/15/16 to 12/20/16 calls and requests cancel. Booking person clicks "Cancel" link for record with Seq=5 and cancels it. Now status  becomes "Canceled" and BookIt link is available.
Please let me know if there are any questions.

Thanks,

Neal


NealPatil @ gmx.com
Photo of Roxanne

Roxanne

  • 142 Points 100 badge 2x thumb
The problem being is that these are fixed weeks and we go by week 1, week 2 etc all the way to 52 weeks - the dates are changing depending upon the year. I guess I am at a loss on how to even begin building the table. WE have the product: Building A with either an Executive suite or Luxury studio, then we have the unit numbers A101 which can be sold 52 times. I started building a table with a pull down menu for the buildings, then the type executive, studio, then a field with the unit numbers, next a field with a pull down menu of weeks 1-52.??? The outcome is when a sale is made and they go to pick a suite it will be greyed out maybe the ones that are not available.  I am not an expert in DB building but I have built our app so far and this is really confusing to me - can't get my head wrapped around flow or even how to start to build
Photo of Neal

Neal

  • 70 Points
Hi Roxanne,
(1)
It looks like you would like to have system which works in a similar way as working with current spreadsheet system. It has advantage that people will not need retraining for using it. Keep in mind that new system can bring many more advantages compared to old spreadsheet system and should try not to miss the advantages of more flexibility of new system.
(2)
I can help you on DB part of your system. If needed you can also contact me offline using my email NealPatil @ gmx.com
(3)
You are on the right track for building products. My recommendation is that you build products table which makes up all different types of products out there.
Example : You mentioned Executive Suite, Luxury Studio etc.
Have two records of Executive Suite and two records of Luxury Studio.
You might have 50 Executive Suites and 300 Luxury Studios, but no need to put it in All=350-products in products table at this time.
Because you will be iterating the following process.
            (A)make the full system
            (B)test it
            (C)Correct the system as a result of step=B.
            (D) go to step=A
(4)
Other data you need is Weeks Data.
You can put four weeks of data to test instead of putting all 52 weeks for testing.
(5)
Document your current spreadsheet system and find out what other items you will need to test your new system and make those items available in new system and iterate the process of testing new system until you are satisfied with new system.
(6)
Once you are satisfied with new system which has subset of your data(i.e. 10 products as opposed to total of 500 products) you can now enter all the data in the new system and iterate the process of testing with full set of data in new system.

Neal
NealPatil @ gmx.com