KITS

[crazybake]

I am not sure where this fits, but I need structure help. I have a solution that allows checkout of assets, but recently I was told that it would be nice to have items that could be in kits (but also potentially checked out individually.) My current relationships are:

 

Tables: Transactions, TransactionLineItems, Assets, Students

 

Transactions:TransID::TransactionLineItems:LITransID

Assets:AssetNumber::TransactionLineItems:LIAssetNumber

 

This is the issue andwhere I need help...I created a new table called Kits and created a relationship with Assets:

Kits:KitsID::Assets:AssetsKitID

 

This works fine as I can add and subtract items from kits. At the same time, all of the individual assets are available to checkout (one by one) by adding an individual asset number when checking out. However, How do I make a Kit show up as an asset? Or a better question.... what is the best way to take an exisiting solution with assets and "kit" them together - while  allowing the possibility of individual asset or entire kit checkout?

[GisMo]

Are Kits and Asset Many-to-Many? If so, create a join table linking Assets To Assets via the join table(call it KitsToAssets). Create a new field in your Assets table to distinguish between a kit and individual. Example create "Assets:IsKit" (as a number) and = 1 for a kit and =0 for individual asset. This way, in your Asset list, you will see the Kits AND the Individual items in the list. When you select a kit, you would load(via a simple script) individual assets from the Kit into the transaction list as well as any individual assets that you add.

[crazybake]

I just cannot seem to wrap my brain around this!! and it seems simple.

 

I deleted the Kits table completely and want to start over - here are the tables:

Transactions, TransactionLineItems, Assets, Students

 

Transactions:TransID::TransactionLineItems:LITransID

Assets:AssetNumber::TransactionLineItems:LIAssetNumber

 

Currently, I am able to create a transaction with multiple line items as students checkout: (e.g., a camera, a lens and a filter, each with a different Asset ID)

I want to have the ability to kit these items together with a different Asset ID- but still allow the individual items to be checked out. I would not have an Asset in more than one kit, nor would I have kits within kits

 

The scripting portion seems easy - it is the relationship creation that I cannot figure out.

 

Background: Currently the Asset ID is a calculation field (text - that combines fields to create a unique ID) - when Assets are checked out, the Asset barcode is scanned or manually entered into a dialog box. The result is validated.

[Kris M]

Maybe not newbie solution but the Hierarchical database model http://en.wikipedia.org/wiki/Hierarchical_database_modelor at least a hierarchical table of assets might be worth a look.

Your asset table could have :

 

pk asset id - auto increment, unique, not empty number

member of kit id - number

asset name or kit name - text

 

this would allow a user to select a kit or an asset from the same table.

 

member of kit id field will contain either null or the pk asset id of the kit or asset associated with the item.

 

Sorry if the description is lacking clarity

[crazybake]

I am very close!!

 

Each Asset now has a field Assets:KitID - if it is empty it uses the Asset:AssetID - otherwise will populate with KitID.

This works and I have created a ported in a related layout that will show the "kit" and its members.

 

However, When students checkout items, I have a dialog that references the AssetID. I need to have it reference both AssetID and KitID so that items can be checked out either as a Kit or individually. That is the missing piece.

[Kris M]

If the member of kit Id is empty then the record is a kit or an asset that is not part of a kit. The field member of kit Id should only be populated for an asset that is a member of a kit.

[crazybake]

Right -

the confusion I am having is regard to the process of checking out. If a student wants to checkout one item from a kit - and not the entire kit, or wants to checkout the kit- how would I "combine" this table table data into one dialog box (one is a kit member id and one is an asset id)

E.g., currently, a dialog requests an input for the Asset ID, a barcode is scanned and it becomes a line item. I will have barcodes for each individual item and barcodes for kits

[Kris M]

I would build a picker portal in a popover or window that would show the assets arranged by kits or not kits and facilitates button click selection of an asset id before scanning. The only logic you would have to nail down is what do you want to happen when someone selects kit A that has assets 1,2,4 as a line item and then selects asset 2 for a second line item. ps you woulde NEVER relate assets to line items by member of kit id. Always use asset id. The only time you might want to relate by member of kit id is when building a picker device.