Portal Droplist Trouble

[fmpgirl]

I have created an inventory database. The two tables that I need help with are the "product" and "sale" tables.

 

OBJECTIVE: When I create a new sale, I would like for the product portal to only display unsold inventory from a droplist to choose from.

 

LAYOUT IN QUESTION: A "product" portal appears on my "sale" table layout. When I click a button entitled "add product to sale" it routes me to the "item number" field (first field) of the "product" portal. I need for that field to be a droplist AND ONLY DISPLAY "unsold" inventory. I have a calculated result field in Products table layout entitled "status" which displays the sold or unsold status for each product.

 

Currently the "item number" field in my product portal displays all of the products regardless of whether they are "sold' or "unsold".

 

The droplist must only show "unsold" because my products are one-of-a-kind.

I can't figure this one out and it is driving me crazy.

 

Would appreciate suggestions. Thank you.

Bryn

[Robby]

Perhaps a new 'item number unsold' calculation field in the Product table could help? A calculation like 'if ( status = "unsold" ; item number ; "" )' will produce a field containing only the IDs of unsold items. Base your drop-down list off this field and you may get what you are looking for. Hopefully your 'sold/unsold' calculation is an indexed field (this may not work for unindexed fields).

Another suggestion: You may need an additional table between the Sale and Product tables. A simple 'Sale Lines' table containing simply a SaleID and ProductID fields would allow you to connect multiple products to a single sale without much work.

- Good Luck

[fmpgirl]

Thanks for the suggestion. I got the same result. I did have the other table your referred to for the line items. The database is actually made up of six tables. Everything works great except for this one droplist. Seems like it should be a simple fix. Fat chance.

 

Anyway, thank you again for your time. If you think of something else, post!

 

Have a great week.

Bryn

[Weetbicks]

First off, for this to work your status calc is probably going to need to be indexed, which means it would either be set via auto-enter calc or script. If it references a related table to get its status then u might be outta luck with the method I'm thinking (hopefully it can be stored!)

 

On sales table, create a calc which is basically set to the word "unsold". Then from your sales table occurrence, create a new table occurrence for products, called "Unsold Product Value List" (for example..) Create a relationship between your sales table occurrence & this.

 

The realtionship would be:

 

Unsold Constant = Status

 

 

The relationship (if you were to put a portal for this on the layout) should only show all unsold products. Base your value list on this relationship.

 

------

 

Is this what you are thinking? I am not sure, but on the products portal, the "Item number" you choose is an inventory ID, and your drop down is to show all inventory IDs (item IDs) NOT on a product record? I think I'm way off in my solution.

 

 

Basically, your Sales/Products is comparable to Invoice/Line Items? The word product is throwing me, but is it basically a line item on a sale? and your item number is actually an inventory ID?

[Robby]

I've build a demo database with a working drop-down list showing only Unsold items. Email me at member1415@mac.com if you'd like me to send it to you.