• You can always expand, add extra fields or columns into table x_Customer to satisfy your needs. BUT DO NOT DELETE THE EXISTING FIELDS.
• Maximum you can add to an database is 64 fields. To add more field open [myNet.Dbase] -> Action. Or use from top main menu myNet.Dbase -> [Column Factory].
• x_Customer: is the table contains all information collected about customers, including: Address, Name, Phone, Email...Currently, it has 38 fields.
x_Customer has the table relationship: 1-to-Many with table x_Invoice.
• x_Invoice: is the table contains all information collected about invoice, including: Delivery Address, Name, Phone, Email...It has 40 fields.
x_Invoice has the table relationship: 1-to-Many with table x_Customer and 1-to-Many with x_OrderDetail. You can always expand table, add extra fields or columns into table x_Invoice to satisfy your needs.
• x_OrderDetail: is the table contains all product information collected for that invoice, including: Product Title, Price, Quantity.
x_OrderDetail has the table relationship: 1-to-Many with table x_Invoice.
See
UNDERSTANDING RELATIONAL DATABASE WITH myNET.DBASE • File names with this icon
are add-on
fields and used for
Member Collaboration. You can delete them if you don't need. • To Expand Tables, Add more Fields In Order To Collect More Info And Collaboration There are 2 different ways:
1) Use myNet.Dbase to add on new Column (or fields). A table should not have more than 50 fields. (See topic: WORKING WITH myNET.DBASE)
2) OR: Create a new tables and setup 1-to-1 relationship with that table. (See HOW TO EXTEND A TABLE BY CREATING A NEW RELATED TABLE)
Picture: Data entry x_customer database table. You can expand this table by add on more fields, maximum 64 fields per database.
Do not delete the fields specified with ( * ) see its property. Select [FORMAT] button = 1 column will display its data type of fields like LST_INVDATE = Date data type .
Then you can expand the table by:
• Use myNet.Dbase feature Add/Rename Column in order to expand this customer table in order to collect more info about the customer.
• OR Create a new table that has the relationship 1-to-1.
• File names with this icon
are add-on
fields, used for
Member Collaboration. You can delete them if you don't
need.• Do not DELETE Field names with this ( * )
| X_CUSTOMER PROPERTY | DESCRIPTION |
| * CUSTOMERID | is the Key. It is the number that represents
that customer in order table. It is = 93 for this customer. • Updated a number by increment to 1 of the last Customer ID number, during an order check-out or member register. |
| * PASSWORD | the password field, minimum 5 characters. It
is used for password of member login. If member forgets password, he can
request send password at Member Login screen. Click on Forget Password. NOTE: Nobody can see the password blacked out even the Administrator. • Updated during an order check-out required enter password field or during member register password field. |
| * SALEPERSON | The name of in-house Sales Rep who take care
of this customer. • Updated a default sales name = INFO_SALES name during an order check-out or member register. |
| *FNAME, *LNAME | The first name and last name of that
customer. • Updated name got from the Check-out Fill Info Form during an order check-out or member register. |
| *UPLOAD_PROFILE | The small thunder nail picture (90x120) of
this customer, or logo uploaded in his [Member Home] • Updated when member logins Member Home and select upload picture profile. |
| *CUST_NOTE | The last time this customer requests some
thing either in ordering check-out or in his [Member Home] login. It is
250 characters. For example, CUST_NOTE could be: "- Contact us regarding
to the account balance." • Updated notes got from the Check-out Fill Info Form Notes input box during an order check-out or member login submit notes. |
| *TAX1_GST_EXEMPT, *TAX2_PST_EXMPT | Tax exemption in Canada. If GST, PST_EXEMPT
is entered then when ever this customer order check-out, will see GST or
PST = 0.0$ on shopping card and in invoice. In other countries, treat
each tax like Tax1, Tax2 if applied. Else ignore. • Updated when member logins and selects Update Account Info. |
| *PAID_BY | Last time of type of payment when he
ordered. There are: Cash, Get Quote, Check, Online Banking, Bill Me
Later, Credit Card, Pay Pal. The last time of order will overwrite the
previous last time of order. • Got from the Check-out Fill Info Form's Paid_By during an order check-out or member register. |
| *ONLINEBANKING_SECRET | The secret used for online banking order. It can always changed from login in [Member Home] |
| *ONLINEBANKING_ANSWER | The secret used for online banking order. It can always changed from login in [Member Home] |
| *CARD_NUM | Credit card number used for online order. It
is recommended that this number should be deleted. It is only piece of
security data that hackers care about. The rest, who cares. • Updated the customer 's card number when select Paid_By = Credit card. If select Pay Pal, this filed is empty. |
| *CARD_NAME, *CARD_TYPE, *CARD_EXP, *CARD_SCC, *CARD_NAME | Info about credit card when this customer
used for order online. If this customer used Pay Pal for ordering then
this info is empty. • All Card_Num and Credit Card Info is created when customer orders select PAID_BY = Credit Card |
| *LST_INVDATE, *LST_INVNUM, *LST_INVAMT | The last time of the invoice number =154,
amount order = 1162.67 (not including tax), and the last date =
2008-09-03 (yyyy-mm-dd). These numbers will show up on the
Check-out Fill Info form if an old member who has bought before. • All related last invoice info is created by updating the last info of that invoice. |
| FLAT_RATE_SHIPPING_FEE | The flat rate amount of shipping fee this
customer granted after the first shipping used option Bill Shipping
Later. Which means that after the first order, you ship to this customer
used option Bill Shipping Later. So you know the address, destination,
and goods of this customers. So you then calculate the last total
shipping fee / total weight = Flat_Rate_Shipping_Fee. Then use this flat
rate in order to calculate next shipping fee. For example, if this
customer delivery address is in San Jose California, US, the first time
order, shipped 60 lbs of goods, cost 50.0$.Thus flat rate shipping is: 60
/ 50 = 1.2 $ / lbs. Thus, if next time it ships 100 lbs, will cost this
customer shipping fee = 100 x 1.2 = 120.0$. NOTE You can
calculate the FLAT_RATE_SHIPPING_FEE based on values of goods too. Which
method ever you like. • Each customer has each different flat rate depending on their address and order products. |
| *REFERRED_BY | It is string of characters of customer IDs
who have referred this customer. For example, if this field = 95, 105,
209 represents customer ids who this customer is referred. Remember in
[Member Home], there is a tool that allows the member add more members. How Does It Work? - When Member John invites a new member Peter to join in your business, your club. An email (Etype=invite) is sent to that member Peter. If Peter accepts by click on the link to a accept form. Then Peter now is stored in a table name x_member_invite. This table contains info like: EMAIL_INVITE: peter@ibm.com - email of person who is invited. CUSTOMERID = 93 of the current member who invites Peter join in. LAST_TIME_SENT: record only the last time the member Dave Smith sent email invite John. STATUS = either not yet, or not sure or accepted. ( See more in How-to Invite A Member )  Picture: Table x_member_invite whenever an current member invites another person join in it is saved in this table. |
| *REGISTER_DATE | The date that this member Dave Smith is
becoming a member. Dave Smith becomes a member only when: • He has ordered some thing. • He is invited by other member or he registered on the website. Even he is not yet to order some thing. The point here is we open membership for marketing and selling purposes. You the owner of the site reserve the rights to cancel membership any time. • Got from the date that new customer order during an order check-out or during member register. |
| *TERM | How long this member will pay: 30 days, 40
days... if paid by Check, or Bill Me Later. Default is 30
days. • Updated a default term=30 days during an order check-out. After that, you must manually enter the true term for this member. |
|
LST_BILL_PAYMENT_DATE |
The date of the last time this customer has
paid. Applied for customers who are granted Paid By: Check or Bill Me Later.
In this case, this customer ID=93, the last time he paid for this
balance is in 2008-09-13. • Updated manually, enter data by in-house accounting staffs. Then create a Member Collaboration, such as Check Your Balance. When member login [Member Home], they will see their account balance. |
|
ACCOUNT_BALANCE |
How much this customer still own. For
customers paid by Check, or Bill Me Later. Default is 30
days.
In this case, this customer ID=93 the last time he paid is in
2009-09-13, and the overdue balance still remains 500.0$ • Manually Created or updated, enter data by in-house accounting staffs. Then create a Member Collaboration, such as Check Your Balance. When member login [Member Home], they will see their outstanding account balance. NOTE: You may want to create a separate table named: x_cust_account_balance that has 1-to-1 relationship with x_customer table. And have in-house staff keep track of accounting balance. It is accounting issues. NOTE myNet.Office is not an accounting software, therefore your accountant must keep track the balance, the date. Then enter data into customer record. myNet.Office will help you create a Member Collaboration, such as Check Your Account Balance. |
| RESOLVE_NOTES | It is the notes the you manually enter about
the account balance. Such as, "- Your balance is 30 due. Please resolve.
Thanks." When members log in. Click on Member Collaboration = Check Your
Account Balance will see this message. You must have a bookkeeping to
keep track the balance. • Updated manually by in-house staff accountant. |
| MEMBERSHIP | Gold, Silver, Bronze, Granted for this
member. • Updated by you enter manually. You can change its name and it values by using myNet.Dbase change or rename column name. |
| * LST_LOGIN | Last time this member customer login. • Updated value last time this member logins into [Member Home] of your website. |
| HISTORY | This is type = TEXT AREA that can contain
65000 characters. Click
to expand. Entered by in-house staffs, the sales
person, any thing history
about this customer. What is his interest. Used for marking purposes.
• Updated by in-house staffs enter manually. |
myNET.DBASE Learn 123 |
Enter the database programming code and
submit in Code Area. Then click [Submit SQL Code] button to get results. • Select CustomerID, Fname, Lname, Email, Phone, Company from x_customer where phone LIKE '%416%' = Will search and list all customers with phone number has included 416. You can apply for different search that has some characters in. Such as, search for all customers with last name, or email = Smith NOTE You can do the same using interface: from right side of Table Admin -> in Search box input type: 416. In the pull down list box that lists all fields of x_customer table: -select a search category - , select Phone -> Click [RUN] button. • SELECT x_invoice.invoiceid, x_invoice.invoice_date as inv_date, x_customer.phone as cust_phone, x_customer.fname, x_customer.lname, x_customer.email, x_customer.address, x_invoice.notes, x_invoice.sub_total from x_customer, x_invoice where x_customer.email ='ddanpros@hotmail.com' and x_invoice.email ='ddanpros@hotmail.com' ORDER BY invoiceid ASC = This code will search in 2 tables x_invoice, x_customer and list all invoices with ID, invoice date, customer fname, last name, email, address, history notes and sub total of each invoice that matches the customer email ="ddanpros@hotmail.com" with all invoices that has same email ="ddanpros@hotmail.com  Picture: The Output of multi tables search. It searches Invoiceid, Invoice Date, and Sub Total in table x_invoice. The rest is in x_customer |
There are 38 fields. So we explain fields that are hard to understand. Fields with * are collected and fixed in code used to collect info when customer order or register. So do not rename. If you see there are need to collect more data from customer, such as Age, Gender, Habit... for marketing. Then you can expand the table by:
• Use myNet.Dbase feature Add/Rename Column in order to expand this customer table in order to collect more info about the customer.
• OR Create a new table that has the relationship 1-to-1. See later: HOW-TO EXPAND A TABLE BY CREATING NEW TABLE
Picture: Data entry x_invoice table.
- You can expand this table by add on more fields or create a new table that has 1-to-1 relationship with this table.
- Do not delete the fields specified with ( * ) see its property. Select [FORMAT] button = 1 column display will see its data type.
| X_INVOICE PROPERTY | DESCRIPTION |
| * INVOICEID | Is the Key. It is the number that represents
that invoice number. • Updated an incremental number of last invoice number during a get quote or order check-out. |
| * INV_TYPE | The type or invoice status of this invoice. There are:
WEB_Complete, WEB_Incomplete, Web_Cancelled. Add more type for your
needs. • Updated as WEB_Complete by default: When ever an order paid by cash, check, bill me later, credit except Get Quote • Updated as WEB_Incomplete by default: For Get Quote check-out only. • Manually enter as WEB_Cancelled by in-house staffs if this invoiced is cancelled. |
| * INVOICE_DATE | The date this invoice is created after a
check-out or a get quote. • Updated the current date after completed check-out. |
| * STATUS | The order status of this invoice. It is
different with INV_TYPE which is the invoice status. It is a option list of : order, quote, back order, pending • Updated as order by default for invoice check-out that is an order. • Updated as quote by default for invoice check-out that is a get quote. |
| * CUSTOMERID | The CustomerID also the Key of table
x_customer of that customer ordered this invoice.
• Updated the Customer ID from exist member when ever there is an order check out. If it is new member, Customer ID is equal to the last customer ID number + 1 |
| * NAME | Name of that customer |
| * DELIV_TIP | For business like restaurants who deliver
locally. You can setup such as it will not show up on the Check Out Fill
Info form. Open the table x_shopping_property. Select No for not display. The default is not display. Default= 0.0$ |
| * DELIV_ADDRESS | The delivery address of this invoice. • Updated the Delivery Address of Payment Info section of the Check Out Fill Info form after check-out. |
| * BILL_ADDRESS | The billing address of this invoice. • Updated the Address of Billing Info section of the Check Out Fill Info form after check-out. |
| * PO_NUMBER | Customer purchase number. You can setup to have or have not on the Check Out Fill Info form. |
| * SHIPPING | The shipping type: D = delivery, S=Ship,
P=pickup. • Updated value from the [Ship Via] input box of the Check-out Fill Info form after check-out. |
| * SHIPPING_WEIGHT_DIM | The total weight or dimension of shipping for this
invoice. It is calculated as sum of each individual weigh of each
product item of this invoice. You
entered the product item weight when using Product Catalog Design Tool
to add a product item in
catalog. (See Day 1). From this total weight, you can calculate the
shipping fee if based on shipping weight. If you choose to calculate the
shipping fee based on % of total order then this weight is not
applied. • Updated value automatically by sum of each item weight of this invoice. Then multiply with the unit weight of shipping fee. Example, if in this invoice has 2 product items. One item weighs = 2 kg. The other weighs 1 kg. The shipping fee is 2.50$ / kg. Then the total shipping fee for this invoice is: (2 + 1 ) x 2.50. The setup shipping is in the table x_shipping_property. |
| * SHIPPING_PRIORITY | It is the type of shipping, such as: Air
mail, Next day delivery, Normal... You can config type of shipping you
want by setup a string of shopping type, such as: "Air mail, Next day
delivery, Normal". Then enter in the filed shipping type in table
x_shopping_property. It will create an option list so that customers
select during check out. • Updated value from the [Shipping Priority] selection box of the Check-out Fill Info form. |
| * SHIPPING_FEE | The total shipping fee for this order. I |
| * TAX2_PST_EXEMPT | The PST exemption tax for this invoice. If
there is PST_EXEMPT then the TAX2_PST_AMT = 0.0$. In other
countries, treat it like Tax2 exempt if applied. NOTE: PST, GST applied for Canadian tax only. To qualify, customer must register as member first. Then select Member Collaboration= Update Your Tax. Before member can buy. TAX2_PST_AMT = 0.0 always if there is PST_EXEMPT. |
| *TAX1_GST_AMT, TAX2_PST_AMT | The PST, GST tax amount in Canada. In other countries, treat it like Tax1, Tax2 if applied. |
| * TIMEOUT | Time that the product is delivery from
warehouse or food
delivery in this case is: 2009-09-05 at 2:38 PM • Updated manually by in-house staffs like shipping department. The default time when check-out is none= 2000-01-01 |
| * TIMEIN_ORD | The time that the product is ordered. • Updated the time of the order after check-out. |
| * ATTENTION | The customer enters attentions, such as: "-
Please ask for John Smith at billing department when delivery comes."
• Updated from the [ATTENTION] box of the Check-out Fill Info form after the check-out. See the Check-out Fill Info form. |
| * CUST_NOTES | The customer enters extra notes for this
invoice. • Updated from the [NOTE] box of the Check-out Fill Info form after the check-out. |
| * SALEPERSON | The name of in-house Sales Rep who take care
of this customer. • Updated a default Sales Person name = INFO_SALES during an order check-out or member register. You must manually enter who is the Sales Person for this account. |
|
*
SALES_NOTES |
The Sales Person enters extra notes for this
invoice. • Updated value manually by Sales Rep during working on Sales Quote Invoice Tool or enter note directly from this data entry. This field is used for Member Collaboration between Sales Rep who want to leave a message for customers for a Get Quote or personal notes so that when Member Login can be seen. See it in Member Collaboration Check Order History List. |
| *PAID_BY | Last time of type of payment when he
ordered. There are: Cash, Get Quote, Check, Online Banking, Bill Me
Later, Credit Card, Pay Pal. The last time of order will overwrite the
previous last time of order. • Got from the Check-out Fill Info Form's Paid_By during an order check-out or member register. |
| *TERM | for this invoice. How long this member will pay: 30 days, 40
days... if paid by Check, or Bill Me Later. Default is 30
days. • Created a default term=30 days during an order check-out. |
|
LST_PAID_DATE |
Time of the last date this invoice is paid.
Usually for customers who paid by Check or Bill Me Later. • It is manually entered the balance by in-house accounting staff. |
|
LST_BALANCE |
The remain balance for this invoice. • It is manually entered the balance by in-house accounting staff.
Member Collaborationif you don't need you can delete Lst_Paid_Date and Lst_Balance. But if you need, then you should create a Member Collaboration Task named it: Check Your Balance of Un-finished Invoices. Then do the collaboration on these 2 two fields. When members login, that member will see all the invoices that are not paid yet including invoice id, last time paid, and remain balance. See How-to Create Member Collaboration in Day 3. |
|
DATE_SHIP |
The date this invoice is shipped. It is for
customers check the shipping arrival • It is manually entered by in-house shipping staff the date of this invoice has been shipped. |
|
DATE_ETA |
The date this invoice is estimate arrival.
It is for customers check the shipping arrival • It is manually entered the balance by in-house shipping staff. |
|
TRACKING_NO |
The tracking number of shipping for this
invoice. • It is manually entered the balance by in-house shipping staff. |
| -- |
Member Collaboration Together, DATE_SHIP, DATE_ETA, TRACKING_NO are used to create a Member Collaboration Task for Tracking Shipping Order History. You will learn how to create this tasks later in Day 3 of How-to Create Member Collaboration. |
• The table contains all detail about that invoice, such as: Qty, Description, Price... From this table, myNet.Office will be able to construct a complete invoice.
• x_OrderDetail has the relation 1-to-Many with table x_invoice.
| X_INVOICE PROPERTY | DESCRIPTION |
| * OID | Is the Key. It is the number that represents each product order of that invoice. An invoice may contain many ordered products. |
| * INVOICEID | It is the number that represents
that invoice number. • Updated an invoice number during a get quote or order check-out. |
| * PRODUCTID | The product ID of that product in that
invoice. An invoice has many product id. • Updated during a get quote or order check-out. |
| * UNIT_PRICE | The price of that product. • Updated during a get quote or order check-out. |
| * QUANTITY | The quantity of that product. • Updated during a get quote or order check-out. |
| * PRODUCT_TITLE | The long title of that product. • Updated during a get quote or order check-out. |
| x_OrderDetail table |
 Picture: from the x_OrderDetail table, invoice 152 used to be a quote now is an order because price > 0. It has 4 ordered products. |
|
myNET.DBASE 123 |
• Run this code in order to get the Invoice
date, invoice number, Invoice Name, and its product order details of the
invoice=152 SELECT x_invoice.InvoiceID, x_invoice.Invoice_date, x_invoice.CustomerID, x_invoice.email, x_invoice.name, x_invoice.Status, x_orderdetail.productid, x_orderdetail.quantity FROM x_invoice, x_OrderDetail WHERE x_invoice.InvoiceID = 152 and x_orderdetail.InvoiceID = 152  Picture: Search multi tables of Invoice and Order Details to get data. Modify the code to get more data. Click 
email that customer. |
• We design simple the order transaction database for you to maintain. When ever there is an order or quote check out, all data of that customer is saved in x_customer. All data of that invoice is saved in x_invoice. All data of order details of that invoice is saved in x_orderdetail.
• myNet.Office collect limited necessary data from customers during a check-out, fields in ( * ) such as: Name, Address, Time Order, Company...If you want to collect more data about customers for your business logic's needs. Then create new tables. Use Member Collaboration to create more tasks for members to collaborate more info.
• Expand table x_customer and x_invoice by add extra column fields for tables. Do not have a table with more than 50 fields. Or you can also expand table by creating new tables then setup a 1-to-1 relationship with x_customer or x_invoice table. See HOW-TO EXPAND TABLE FOR YOUR NEEDS
• Collaborate with member customers in many ways, such as: Check Your Account Balance, List Order History, List Invoice Outstanding Balance, Track Invoice Order And Shipping, Update Your Account Info, Change Password, Update Deliver Address And Billing Address, Submit And Tracking RMA, Upload Pictures And Files...
• Upgrade operations and customers support take advantages online. We understand that the way you have built your in-house old infrastructure for many years ago it is hard to change. But you don't have to change. Open an extra new online model. You must learn to adapt to fast changing world. You just can't stay the same for ever.
• Tech Data runs 20 B complex business online then your small business should be able to do so online.