Foodclub:Documentation:Database fields

For users who choose to maintain their own private product database using phpMyAdmin, this document explains each field in your private_<account-name> table.

Thanks to Sally Zimmermann of Nourishing Connections Food Club for this document.

code

Character 16
Product code
Foodclub software treats this as a record key. When combining products for the Group order, like product codes are combined.
Warning: if you change a code once orders have been placed, your order most likely will not merge together as it should. If you are going to change product codes, the best time to do this is after an archive and before new orders come in.
Some companies (e.g. Frontier, Green Pastures) already have product codes assigned to their products. If this is the case, we use their product code.
Some companies (Toigo, Tuscarora, usually the really local ones) do not use product codes. In this case, we assign easy unique codes. Keep in mind that product codes are alphabetized in the Group order and Final order and we want like items grouped together for readability. Some examples:
- apples1
- apples2
- apples3
- potatoes1
- potatoes2

sub_category

Character 128
Displayed in the “product search” function. Some examples:
- Gala
- Nitany
- Red (potatoes)
- White (potatoes)
Also used in product “browse” feature where you can browse by category, then sub_category, etc.
This field is also displayed on the Group order and Final order pages, but is hidden by default. You can "unhide" it using CSS.

sub_category2

Character 255
Displayed in the “product search” function, but is hidden by default. You can "unhide" it using CSS.
Also used in product “browse” feature where you can browse by category, then sub_category, etc.
This field is also displayed on the Group order and Final order pages, but is hidden by default. You can "unhide" it using CSS.

manufacturer

Character 255
displayed on the Order, Group order, and Invoices pages.
Consumers like know where something is coming from.
When we are dealing with a local orchard, all products from the orchard will simply have the orchard name. For example: Toigo Orchards

description

text of unlimited length
displayed on the Order, Group order, and Invoices pages
Be as descriptive as possible. On seasonal items, it is helpful to say what the season is.

short_description

character 128
currently only displayed on Simple sorting sheet, linked from Splits and Invoices

size

character 32
The number is used only in split processing. Everyone orders part of the size and when the parts add up to the “size”, the item gets ordered. The size field must start off with a number and whatever comes after the number is ignored by the software, and is only useful for human readability. Examples:
- size: "12 jars/case" – Sally orders 4, Elizabeth orders 2 and Cyndi orders 6. It is understood that the quantity the user enters is in the same units as "size"
- size: "10 lbs" - Sally orders 6 lbs, Elizabeth orders 2 lbs and Cyndi orders 2 lbs.
This number is displayed on the full case Order page, but not used for anything. If you will not be doing splits with the item, you could leave the field blank or put whatever you want in it. Often we choose to fill size with something informative for the user - e.g. 8 oz.

case_units

integer
meant to designate the number of individual units in a multi-unit item - e.g. 12 (for a case of 12)
not currently used in user-maintained databases – leave blank or set to NULL if you do not have it

each_size

character 24
meant to designate the size of an individual unit in the multi-unit item (case) - e.g. "8oz bottle"
not currently used in user-maintained databases – leave blank or set to NULL if you do not have it

unit_weight

decimal
meant to contain the actual weight of an individual unit in a multi-unit item (case), or the weight of an item that is not packaged by the case
For items set "is_priced_by_weight" = 1, this number is used for the default weight on the Invoice processing page, but only for full-case orders placed on a user's Order page. The default can always be manually changed.

case_weight

decimal
meant to contain the actual weight of the entire multi-unit item (case)
For items set "is_priced_by_weight" = 1, this number is used to display the default weight of split items on the Invoice processing page. The default can always be manually changed.
When invoice fees based on shipping weight source setting is enabled, this is also used as the default "Gross dry ship weight" on the Invoice processing page. The default can always be manually changed.

price

decimal
this is the price of a quantity of one of an item
- so if the item is sold by the case, that means case price
- but if it's an individual item then the price would be for one of them
it should also be noted this is not necessarily always the price that the item will cost at delivery, since sometimes the sources (notably distributors) change their prices

sale_price

decimal
sale price is only displayed and used for private databases if explicitly enabled on the Product database page. If enabled, it displays in red, and calculates the discount from normal price (above). Also, "sale_price" is used instead of price when adding an item from a search to a user's order or splits.

unit_price

decimal
meant to indicate the price of an individual item inside of a case priced item (assuming the price field is for case price)
if the item is not sold by the case, then this field would either be left as NULL or set to the same as price
shown in "product search" results if enabled in the Product database settings.

retail_price

decimal
not currently used in user-maintained databases

price_per_weight

decimal
If "is_priced_by_weight" = 1, this number is used for the default price per weight on the Invoice processing page. It can be manually changed on Invoice processing.
If "is_priced_by_weight" = 0, this field is ignored

is_priced_by_weight

boolean
Most items are not priced by weight (e.g. container of laundry soap, Toilet paper, jar of pasta sauce, etc.) In these cases the flag should be set to “0”
Farm goods are often priced by weight (when you order 1 lb of ground beef, you usually get slightly over or under and get charged for the amount you receive). In these cases the flag should be set to “1”

valid_price

boolean
not currently used in user-maintained databases – only has an effect in private databases without phpMyAdmin enabled

taxed

boolean
not currently used in user-maintained databases

upc

character 24
not currently used – set to NULL
there only for back-end purposes for official distributor databases

origin

character 64
shown in "product search" results if enabled in the Product database settings.
should be set if the country of origin is not the U.S. For example:
- In the Frontier catalog some items are from other countries and folks like to know where their products are from. In Frontier, country of origin gets displayed with the product search

image_url

character 255
Must be set to a full (external) URL of a full-size image for the product
- e.g. http://my.server.com/images/prod001.jpg
Will only have any effect if the "Show images in search results" option is enabled in Product database settings
Will only have any effect if "thumb_url" is also set for the product

thumb_url

character 255
Must be set to a full (external) URL of a thumbnail-sized image for the product
- this will be shown on each product row in the search results, so make sure the image dimensions are small enough
- e.g. http://my.server.com/images/prod001-thumb.jpg
Will only have any effect if the "Show images in search results" option is enabled in Product database settings
Will only have any effect if "image_url" is also set for the product

num_available

integer
Meant to be used if you want to limit the quantity of items ordered by users, or to enforce out-of-stock items (num_available = 0)
Only has an effect if enabled in Product database settings
If set to NULL (all capital letters), the software will not use it, even if the Show num_available setting is enabled on Product database page.
If you have the num_available setting enabled in your Product database settings, do not leave this field blank or set it to "null" (lower case letters), as the CSV import process would convert that to 0, and the item would be flagged out-of-stock

For out of stock items

Set to “0” when an item is out-of-stock and the end user will not be able to order it. Some examples:
- In our orchard database items go in and out of stock on a seasonal basis. Rather than deleting an out-of-season item from the database we choose to set num_available to “0”. Usually in the description field we give it’s seasonal dates. At least this way the user can figure out what is coming when.

To count down inventory

The software will automatically count down when items get ordered. Works across all clubs ordering from a regional database.
Note: this feature does not yet work with splits. So on the Splits page, when cases fill, the num_available will not be reduced by one. Support for num_available in Splits is on the to-do list.

valid_order_increment

decimal
For input error checking on Order page. Users will only be able to enter multiples of this number when ordering. Examples:
- It is usually set to 1. This means the user can only enter 1, 2, 3, 4,… for the “Quantity” field on the order form.
- valid order increment = "1" - the user can only enter integers for the "Quantity' field on order form
It can also be used to hide an item from the product search/browse results:
- if set to 0 (as opposed to NULL), it causes the item to not show up in the Order search/browse results, as if the item were not in the database.
This field is ignored unless the Enable user input validation when adding items from search on users' order form is set on Product database page.

valid_split_increment

decimal
For input error checking on Split requests. Users will only be able to enter multiples of this number when ordering splits. Examples:
- valid split increment = ".25" - the user can only enter .25, .5, .75 for the “Quantity” field on “split request”
- valid split increment = "1" - the user can only enter integers for the "Quantity' field on "split request"
Think of the people who will be sorting the item, and make sure their job will not be too difficult.
It can also be used to hide an item from the product database search/browse results:
- if set to 0 (as opposed to NULL), it causes the item to not show up in the Splits search/browse results, as if the item were not in the database.
This field is ignored unless the Enable user input validation on split requests is set on Product database page.

last_updated

timestamp
Do not set
this is shown under the Valid as of column in search results. If you don't enter anything in phpMyAdmin, or in an import file, it will get set to the date and time you enter/import the item.

last_ordered

timestamp
not shown anywhere in the system, except for unmaintained (private w/o phpmyadmin) databases
This is set when the product database gets updated from archiving an order. For that to happen, either phpmyadmin needs to be disabled, or the setting to allow update of product database needs to be turned on.

num_orders

integer
not currently used in user-maintained databases
for unmaintained databases (when phpMyAdmin is not enabled), this is only shown in the Orders column in search results for private databases, and gets updated when archiving the order.