Jump to content

Relationships in rental DB


Chrisbee
 Share

This topic is 5730 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi,

I'm a newbie working on an equipment rental project, and I'm having some trouble with my relationships (aren't we all?). I've seen a few similar posts, using three tables, but the solution I'm working with has four tables, as follows:

Customer (primary key = CustomerID)

Invoice (primary key = InvoiceID)

LineItems (primary key = LineItemID)

Equipment (primary key = EquipmentID)

The attached image file shows the current setup of my tables.

I'm confused about how to add Equipment data to the invoices, and I basically have two problems (that I know of, anyway)...

First, I'd like users to enter an equipment name in a portal on the Invoice layout and then have a popup within that LineItem that shows them a list of available SerialNumbers from which to chose. With the LineItems table linked to the Equipment table via the EquipmentID, I'm not sure how to get the LineItems layout to see the available SerialNumbers without an EquipmentID being assigned. I can get the mechanics of the portal and popup working by using a LineItemName field to set the name of the equipment and then run a script that finds all the available SerialNumbers in the Equipment table whose EquipmentName matches the LineItemName. The only problem is that the LineItems table can't see the results of the search in the Equipment table because no EquipmentID is assigned until after the user selects one.

The second problem is that the people for whom I'm creating the database want to enter their own serial numbers; that's why my Equipment table has two "serial number"-type fields -- EquipmentID and SerialNumber. Everything I've read so far about primary keys has said to always use an auto-generated key to make sure the key stays unique, but I'm not sure how to properly relate their manually entered SerialNumber to my EquipmentID. By the way, I do validate the SerialNumber they enter to make sure it's unique.

Am I even remotely on track with my table relationships? Can anyone provide any suggestions or insight?

This is my first post ever, so if my questions aren't clear, or I've somehow managed to offend anyone and/or everyone, please go easy on me.

Thank you all in advance for any help you can provide!

Chrisbee

Inventory.JPG

Link to comment
Share on other sites

Hi comment,

Thanks for responding! I mostly understand what you mean but want to clarify.

Would TypeID be a unique, auto-generated ID or the category of equipment? If it's auto-generated, I'm not sure how to use it in the LineItems table because the user wouldn't know the TypeID values. If, on the other hand, TypeID is an actual type of equipment (by name), that would work from the user's standpoint.

I'm also still not sure how the LineItems table would see the ProductID if the user hasn't picked a specific item yet. What am I missing?

Thanks again for your help!

Chrisbee

Link to comment
Share on other sites

It's all about value lists, for both questions. I'll address your second question first:

First, the user selects a category (ProductTypeID). He/she does so from a value list listing ALL values in the field ProductTypes::ProductTypeID. Once a choice has been made, a relationship is established between LineItems and ProductTypes (and by extension, also between LineItems and Products).

Now we need another value list to pick the actual product. This will be a value list of values from field Products::ProductID, showing only related values starting from LineItems. Once this choice has been made, a relationship is established between LineItems and SelectedProduct.

Regarding your first question, I prefer auto-generated serial IDs for relationships. Just think of someone deciding to rename a category, or even just discovering a typo.

A value list can be defined to show a value from a second field, so the user knows what he/she is choosing. In version 8, you can choose to hide the serial ID completely from the user (there are methods to achieve the same effect in previous versions, but that is a user-interface issue).

Link to comment
Share on other sites

I’m ba-a-a-ack…

I thought I understood, but things aren’t working exactly right, so I apparently don’t understand completely -- I guess I’m still a bit confused on how the SelectedProduct TO works.

I added a drop-down menu from which the user can select the LineItems::TypeID, and this list correctly displays all values from ProductTypes::TypeID. Then I added a popup list field that displays data from LineItems::ProductID and correctly shows only related records from a second value list that uses values from the field Products::ProductID with its view starting from table LineItems. This all seems to work properly.

The problem is that, when I select a TypeID, the Products::ProductID field is automatically assigned the first ProductID value that occurs in table Products. For example, if I have ProductID values A-001 through A-999 in table Products for a particular TypeID, A-001 always appears as the ProductID in table LineItems regardless of which value I choose from the LineItems::ProductID popup. The ProductID gets assigned as soon as a TypeID is selected, and selecting items from the popup has no effect on the assigned ProductID. How do I get my choices made in the popup list to update in the LineItems records?

Another problem is that the popup shows all related records for ProductID so if the user chooses a particular TypeID and ProductID on one line of the portal, that ProductID is still available if they choose another of that same TypeID. I tried attaching a button action to the ProductID field popup, and I can run a script that checks which ProductIDs are available in inventory (i.e. Product::Status = “In”), but I can’t seem to get my found set to relate to the popup. How can I relate the popup to a found set?

Thanks in advance for any help you can provide!

Link to comment
Share on other sites

I lost you somewhere in the middle of your explanation, I'm afraid. It seems you need to make sure that the fields entered are fields from LineItems. If that's not it, attach your file so we can see what went wrong.

The second question is a bit more complex. You cannot "relate the popup to a found set", but - as you have seen - you can make a value list show values from related records only. So perhaps it could be as simple as adding a criterion to the relationship, to include only products with "in" status?

Link to comment
Share on other sites

Here's my file... Thanks for taking a look!

The portal in question is on the DeliveryRequest layout. The drop-down menu correctly adds the selected equipment type, and the popup shows all of the related serial number records (the primary key, EquipID, is hidden in the list); however, when I select a serial number, the choice isn't reflected in the LineItems record (tblRequestLineItems).

Also, can you elaborate on which relationship might have a criterion to include only products with a status of "in"?

Thank you again for your help!!

Chrisbee

Inventory.zip

Link to comment
Share on other sites

The field to enter EquipmentID should be EQUIPID, not EQUIPSN.

BTW, the EquipName field in Equipment is redundancy - this information is already known from the type, no need to repeat it.

I have added a calc field and changed a relationship, so that only "In" equipment can be selected. This is not the only way to do this, just an example.

Inventory1.fp7.zip

Link to comment
Share on other sites

Thank you again SO MUCH!! The popup works now, and I didn't realize I could use a calculation in a relationship that way.

I have one last question, and then I'll stop making such a pest of myself.

Everything seems to be working correctly now -- when I select an EquipID from the popup, it associates itself to the correct EquipID and EquipSN in tblEquipment.

However, when I add the tblEquipment::EquipID and tblEquipment::EquipSN fields to the tblRequestLineItems layout, that layout shows incorrect values for these fields for each of the Line Items records. Shouldn't tblRequestLineItems show the same values as tblEquipment since they're linked?

Do you know of any good relationship books that you can recommend. I obviously need to do some work in this area.

Thank you again!

Link to comment
Share on other sites

This topic is 5730 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.