conditional VL blues...

[sleep deprived]

I've been swimming circles around this conditional VL problem for a while, and just can't seem to figure it out. I'm attempting to put together a bidding/proposal program. I've got my product line broken down by category and subcategory. What I would like to do when bidding a project is select a category, which would create a conditional VL for my subcategory, which in turn would create a conditional VL of related subcategory products. The problem is I can't seem to get that last conditional VL to work.

Here's what I've go so far:

Leads:

id_lead

lead_name

Vendors:

id_vendor

vendor_class (VL)

vendor_name

Product Categories:

category (VL)

subcategory

Product List:

id_product

vendor_class (VL)

vendor_name (conditional VL)

product_name

markup_rate (VL)

unit_cost

markup_price (calculation)

Product Builder:

id_product

product_name

category (VL)

subcategory (conditional VL)

Bid Builder (Line Item)

id_proposal

id_proposal_items

id_lead

id_product

lead_name

category

subcategory

product_name

quantity

unit_cost (lookup)

unit_cost_ext (calculation)

markup_price (lookup)

markup_price_ext (calculation)

Proposals:

id_proposal

id_lead

lead_name

[bid Builder Line Item portal]

category (VL)

subcategory (conditional VL)

product_name (conditional VL)

quantity

unit_cost

unit_cost_ext

markup_price

markup_price_ext

Honestly, I don't completely understand how a Line Item file works (I just know I need it). I'm fairly sure the problem resides somewhere between the Bid Builder Line Item and Proposal files. Any help would be greatly appreciated!

bidder_test.zip

[PeterHW]

Hi,

I initially figured the solution would be fairly straightforward, but the further I came, the more I realized that your remark regarding not understanding what the line items file was for is key to your problems. For instance, the fm_proposals file contains a portal to fm_bid_builder (the 'line items' file). However, you are using fields from fm_proposals inside that portal, which is not the way this should be used and is also the cause of the VL's not working correctly. I've made some changes to the files you had sent, namely fm_proposals, fm_product_list and fm_bid_builder. I've marked VL's and relationship with the prefix NEW to indicate which ones I made.

Understanding 'line items' files, or 'join files' as they are typically called, is essential before you can effectively use them. The purpose of a join file (or table) is to join two tables. That means that each record in the join table reflects the unique combination between a record from table A and table B.

So let's say you have an invoice file and a products file, the join table would contain a record for each product (line) that is related to the invoice record.

Also see this recent thread for additional info.

Regards,

Peter

bidder_test.zip