difficulty with conditional value list

[nelsonkh]

I have looked at a few sample files uploaded in some previous threads, but still am a little confused about what mistake I am making with my file.

I have three tables, with the following fields:

Customers

Job Id

Name

Material Items

Material Category

Material Description

Material Price

Job Id

Material Pricing

Material Category

Material Description

Material Price

Material Pricing is set-up with each of the categories, items and prices for our list of possible supplies.

I have a portal in the Customers layout for Material Items. I have it set-up so that the Material Items & Material Pricing are joined by a Material Description = Material Description relationship, so that the price from the Material Pricing can be copied in to the Material Items table.

I want to set-it up so that in the Customers Layout, on the portal, we can select the Category, and then a drop-down with the corresponding Items will show up, and the price will populate.

If I have it set so that Material Description = Material Description then the prices populate, but I cannot get the category drop down to give us the appropriate options that are ONLY for that category. If I change so that Material Category = Material Category between those tables then I can get the drop downs to work, but the prices don't convert. Do I need a Table Occurence? I tried a few different variations of this but could not get it to work.

[bcooney]

My advice is to study the Invoice demo that I posted for your other thread. You should be matching on IDs not text values. Why is there a JobID in Customers and not a CustomerID? Do Customers have Jobs? Then you need those two tables. What is the parent table of your Material Items (which looks as if it's a line item table)?

[nelsonkh]

With our database 1 customer = 1 job -- customers don't have multiple jobs (actually, They can, but we run everything off a job number, and then these materials are assigned to a job). The Job ID is matching between the Customer and Material Items tables. I could assign a Material ID in both the Material Items and Material Pricing tables, but I don't see how that would allow me to both pull prices over, and generate a value list of items assigned only to a specific category of materials.

[bcooney]

You need a Customer table. Each Job record has a relationship to Material items by JobID, and a relationship to Customer by CustomerID.

Set the Material Price in the Material Items table to a number field with the entry option to lookup, when MaterialID matches MaterialID.

I don't understand this:

"If I have it set so that Material Description = Material Description then the prices populate, but I cannot get the category drop down to give us the appropriate options that are ONLY for that category. If I change so that Material Category = Material Category between those tables then I can get the drop downs to work, but the prices don't convert. Do I need a Table Occurence? I tried a few different variations of this but could not get it to work."

Are you saying that you have different pricing for a given MaterialID based on its Category?

[nelsonkh]

No, sorry - I have unique prices for each material option. But, my materials fall into categories, i.e.:

Shingles

CTR 1

CTR 2

CTR 3

Underlayment

Roofer Select

30 lb Felt

15 lb Felt

Fasteners

1-1/2"

1-1/4"

Plastic Caps

So, in the Material Items portal in my Customer/Job layout, I would like to be able to enter in the Category (i.e. Fasteners) and in the Item Description field have a drop-down that ONLY shows the items in that category (1-1/2", 1-1/4", Plastic Caps). Then the corresponding price should come up, based on the price associated with the category-item description match in the Material Pricing table.

You need a Customer table. Each Job record has a relationship to Material items by JobID, and a relationship to Customer by CustomerID.

Set the Material Price in the Material Items table to a number field with the entry option to lookup, when MaterialID matches MaterialID.

I have my three tables set-up this way, and my customer table is my job table (it is actually job numbers - I just call it customer table out of habit because that's what we call it in the office - sorry!)

[comment]

Sounds very similar to this:

http://www.fmforums.com/forum/showtopic.php?tid/200824/

[bcooney]

Very similar. Now I understand what he's trying to do. --whittle down the list of products to choose from based on category selected.

Please consider, nelsonkh, creating a Category table and storing a CatID in your MaterialsPricing table. This way, you can change the text of the category without a problem.

Another way to do this interface would be to have an Add Item button that shows a popup window of Products, and allows for a Find. Then the user selects a product, the window closes and sets the product ID in the line items table. This is an option to consider, if even after selecting a category, the product list is still quite long.

[nelsonkh]

Thank you, both bcooney & Comment. I re-arranged my tables and table occurrences, and have my category and subcategory drop downs working now. I am still having trouble with the permission group setting for the department who should be using this. When they go to add a portal row or choose a subcategory (material description field) it tells them they do not have access privileges to perform this function. However, I do, so it is not a matter of allowing creation of records between tables, I don't think. I have it set so that this department can create, view, edit and delete records from the table...any clue as to what would be preventing them from editing / adding records?

ETA: I checked under the permission settings for this department and if I go directly to the layout for the table, I can edit, add, delete, create records, but I just cannot do it through the portal.

Edited February 16, 2009 by Guest