Camping Inventory - assign a 'pack list' to a given trip - ?Relationship

[db_tragic]

Just can't quite seem to get my head around this - though it will likely be a "Doh!" moment when someone comes up with advice.

 

Scenario is as follows:

I build an inventory of items I keep for camping.

For each trip I do, I want to keep a track of what I plan to take/took.

 

My initial thoughts were:

3 tables (with fields)

 

1. Inventory (contains fields such as Item, Category, Stored Location, Packed Location)

2. Trip (Trip name, Date, Duration, Season, Expected weather, Actual Weather)

3. Packlist (To Pack?, To Buy?, Packed?)

 

The way I want it to function is this:

 

I build up an inventory of camping equipment.

I create a new trip, then go to a related list-based layout, showing my inventory (organised say, by category), which includes all the Inventory fields mentioned above), but also 3 extra fields (formatted as a Yes/No popup) - "To pack?", "To Buy?", and "Packed?"

 

I want to be able to review what I had packed for earlier trips - this is a key function.

 

Just can't get my head around the relationships graph, in a way that keeps the inventory itself available to all trips, but in a way that I can record what was packed on each trip.

 

Initial thought was something like below;

 

 

Any thoughts?

 

TIA

[comment]

The PackList needs to be a join table between Trips and Inventory. This is quite similar to an invoicing solution: inventory = products, trips = invoices and pack list = line items. You buy (pack) a selection of products (inventory items) every time you make a purchase (trip).

[db_tragic]

The PackList needs to be a join table between Trips and Inventory. This is quite similar to an invoicing solution: inventory = products, trips = invoices and pack list = line items. You buy (pack) a selection of products (inventory items) every time you make a purchase (trip).

 Thanks comment,

 

Ok, so I've rearranged the relationships (see end of post), but have struck a challenge setting up a Pack list against a trip.

 

I've set up a 'start screen' which includes  buttons to view a list of Trips entered, or create a new one.

 

In the case of creating a new trip plan:

I want to go to a layout called CategoryList, (a list view of the inventory, by category), which would show all items in the inventory, on separate lines, and on the same line as each inventory item, three "Yes/No" fields corresponding to "To pack?", "To Buy?" and "Packed?"

 

First, I need to clarify what table this list view should be based on. If I base it on anything other than the Inventory table, I don't get a list of the inventory items, however, when I do base it on the Inventory table, the three yes/no fields are inoperative (does nothing on click). See below

 

 

The button script for 'Create New Pack List" is:

Go to Layout ["List By Category"(Inventory)]
New Record/Request

My relationships are as follows;

 

The key issue I feel I am having is getting around the idea of displaying the whole inventory, whether I'm viewing an existing packing scenario, or creating a new one. When I create a new pack list, I just want to check off what I'm wanting to pack, etc, against each item in the whole inventory.

[Luis Francisco Rivero]

Your join table should have Many To One relationships to either side. Inventory -< Pack List >- Trip.

The logic behind this is that you can pack each item on the inventory many times and you pack many items for every trip. The way you have it set up now you can pack every item on the inventory many times and each time you pack it it is for many trips.

The way to fix this is that your Join Table (Pack List) should be related to your other tables through their primary keys (which would be foreign to the join...)

Inventory -< Pack List >- Trip

_pk_InventoryID -< _fk_InventoryID // _fk_TripID >- _pk_TripID

[comment]

The key issue I feel I am having is getting around the idea of displaying the whole inventory, whether I'm viewing an existing packing scenario, or creating a new one.

 

I'd suggest you get it working first using a drop-down/pop-up to populate a portal to PackLlst, placed on a layout of Trips. Then ask about fancying up the UI.

[db_tragic]

Your join table should have Many To One relationships to either side. Inventory -< Pack List >- Trip.

The logic behind this is that you can pack each item on the inventory many times and you pack many items for every trip. The way you have it set up now you can pack every item on the inventory many times and each time you pack it it is for many trips.

The way to fix this is that your Join Table (Pack List) should be related to your other tables through their primary keys (which would be foreign to the join...)

Inventory -< Pack List >- Trip

_pk_InventoryID -< _fk_InventoryID // _fk_TripID >- _pk_TripID

 

Thankyou for that - starting to make sense now.

 

I'd suggest you get it working first using a drop-down/pop-up to populate a portal to PackLlst, placed on a layout of Trips. Then ask about fancying up the UI.

 

I've not really considered using a portal at this stage, only because I prefer the visual appearance of a simple list of inventory items, with a set of radio buttons on the same line as each item. (I did this in a simpler version of this database, which only had the one table (Inventory) and didn't include attributing a pack list to a given trip. Adding the Trip table, (and separating Pack List into another) which I'm doing now, I'm trying to keep to a similar format if possible.

 

Ok, so I've set up the relationships as Luis suggested. In the 'Trip Edit' layout, I've added a button "Create New Pack List", with the following script:

Set Variable [$TripID; Value:Trip::_pk_TripID]
Go To Layout ["Pack List" (Pack List)]
New Record/Request
Set Field [Pack List::_fk_TripID; $TripID]

which, takes me to the layout below (shown in edit mode)

 

 

What I want, is for this layout to show all existing inventory items in a list, so all I have to do is tap the appropriate radio button, and record that item's 'pack status' within that Pack List.

 

How do I go about getting the items to populate automatically from the 'inventory'(Including the 'Category', PakLoc and StorLoc fields)?

Or, is this where I'm going to have to use a portal, like you suggested comment?

[comment]

What I want, is for this layout to show all existing inventory items in a list,

 

That's not really possible, because you are in a List layout of the PackList table. There is no room to show another list. What you can do, is use two portals: one showing all Inventory items, the other showing the selected items ( i.e. related records from the PackList table). You need to be on a layout of Trips for this to work.

 

However, this is a matter of building a more elaborate user interface. I suggest again that you get this (i.e the relational infrastructure) working with a simple UI first. Once you are sure you have it working (and that you understand how it's working), have a look at:

http://fmforums.com/forum/showpost.php?post/355429/