Difficulty relating tables

[tnorbut13]

Newbie question here..(I know some of you will bang your heads against the wall, but here goes)

I am trying to set up a database with the following relationships:

RFQ (Main table)
Product Inquiry (many to one with RFQ)
Vendor (Many to one with Product Inquiry)
Pricing (Many to one with Product Inquiry and Vendor)

Here's my problem. I can't get the relationships right in Xataface. Each product inquiry should have the ability to have multiple vendors for each product selected. Each vendor should be able to have multiple pricing for each product (we price by quantities, ie Product inquiry will have a break down of 1000, 2000, 3000 etc and the vendor provides corresponding pricing for each break)

Everything else I've set up for this database works great, I'm just hung up on this area and unfortunately this is the meat and potatoes of what we're trying to get. Please help.

[shannah]

Can you post what you have in your relationships.ini files right now?

[tnorbut13]

RFQ Relationship:

[Customers]
Customer.Customer_ID = CustomerRFQ.Customer_ID
CustomerRFQ.RFQ_ID = "$RFQ_ID"

[Sales]
Sales.Sales_ID = SalesRFQ.Sales_ID
SalesRFQ.RFQ_ID = "$RFQ_ID"

[Part]
Part.Part_Request_Number = PartRFQ.Part_Request_Number
PartRFQ.RFQ_ID = "$RFQ_ID"

[Purchasing]
Purchasing.Purchasing_ID = PurchasingRFQ.Purchasing_ID
PurchasingRFQ.RFQ_ID = "$RFQ_ID"


Part Relationship:

[Vendor Pricing]
__sql__ = "SELECT * FROM PartVendorPricing, Vendor, Pricing WHERE Vendor.Vendor_ID = PartVendorPricing.Vendor_ID AND Pricing.Pricing_ID = PartVendorPricing.Pricing_ID AND PartVendorPricing.Part_Request_Number = '$Part_Request_Number'"

Vendor Relationship:

[Pricing]
Pricing.Pricing_ID = VendorPricing.Pricing_ID
VendorPricing.Vendor_ID = "$Vendor_ID"

Nothing in Pricing Relationship

I'm trying to get all of this to come up under the RFQ relations for end user ease of use. Right now I have things work the way I want them to (ie Start an RFQ, associate a sales person, purchasing person, customer and multiple parts) but associating vendors then ultimately the vendor pricing with the part that is attached to the RFQ is where I'm running into issues.

[shannah]

I only notice one thing that jumps out at me. That is the Vendor Pricing relationship. Relationship names can't have spaces. If you want to change the label of a relationship you could add
action:label = "Vendor Pricing" and then change the name of the relationship to [Vendor_Pricing] or something along those lines.

Can you describe what IS happening? Do you get any error messages? Do you just get the wrong things coming up? etc..

-Steve

[tnorbut13]

When I go to the 'Add a new Vendor Pricing' Vendor Pricing tab, I an empty select field, vendor name, vendor email (both from Vendor table) and an empty PartRequestID (Part Request Primary) field.

[shannah]

This is kind of a tricky relationship. If you look at it, the Vendor and Pricing records are bound by the __sql__ query. I.e. given a Part_Request_Number, both the Vendor and the Pricing records are determined.

So adding a new related record here will result in creating new Vendor, Pricing, and PartVendorPricing records all in one go. I suspect that you are looking for something a little more elegant than this (as you may want this relationship to be using an existing vendor but creating a new Pricing record.

Perhaps a simpler way to organize this relationship would be as follows:

[Vendor_Pricing]
PartVendorPricing.Part_Request_Number='$Part_Request_Number'
Pricing.Pricing_ID=PartVendorPricing.Pricing_ID

Now, since this relationship is defined on the Part table, I'm assuming that your Part table has a field named Part_Request_Number.

This will effectively produce a list of Pricing records associated with a given Part. Each row will contain a vendor id to mark the vendor that this pricing record is associated with. You can use a select list and valuelist to make the vendor_id field of the PartVendorPricing table into a select list with vendor names to make it more user friendly.

For example.

-Steve

[tnorbut13]

OK. Tried the relationship as you described and got the following message:

Warning: Error parsing /var/www/rfqtest/tables/Part/relationships.ini on line 2 in /var/www/xataface/Dataface/ConfigTool.php on line 331
[pear_error: message="Parse error: Unexpected clause on line 1 select * from PartVendorPricing where PartVendorPricing.Part_Request_Number='$'' ^ found: "'"" code=0 mode=return level=notice prefix="" info=""]
Fatal error: Cannot use object of type PEAR_Error as array in /var/www/xataface/SQL/Parser/wrapper.php on line 649

[tnorbut13]

'Slaps forhead'

Nevermind. Forgot to change ' ' to " ". All better now. Thanks!

[shannah]

Sorry.. looks like you need to use double quotes.. not single quotes.

[tnorbut13]

1 more question. Where should the delegate class go for the select? In the vendor folder or vendorpricingpart?

[shannah]

Good question. I'm not sure off the top of my head, so the correct answer is both. You don't really need to use a delegate class for this. Just a valuelists.ini file. If you add the valuelists.ini file to your application directory (not in any particular tables directory) the valuelists defined therein will be available from all tables.

Then just set up the fields.ini file to use your valuelist in both tables' Vendor_ID field.

-Steve

[tnorbut13]

Well, things *almost* work now.

I enter a new RFQ, add a part, and go to the VendorPricing tab on the part request. I have set up the select so that I can choose the Vendor Name when I add new pricing to the Part. However, on this tab there is now an empty 'Part_Request_Number' field and when I click save, the Vendor_ID remains empty as well. So I can create a new Pricing record, but it remains unassociated from the Part_Request_Number and from the Vendor.

[shannah]

This is very strange. Can you post the SQL schemas of the associated tables so I can take a look?

[tnorbut13]

SQL for Part

CREATE TABLE IF NOT EXISTS `Part` (
`Part_Request_Number` int(11) NOT NULL auto_increment,
`XXXX_Part_Number` varchar(200) default NULL,
`Customer_Part_Number` varchar(200) default NULL,
`Description` varchar(250) default NULL,
`Material_ASTM` varchar(200) default NULL,
`Quantity_1` int(11) default NULL,
`Quantity_2` int(11) default NULL,
`Quantity_3` int(11) default NULL,
`Quantity_4` int(11) default NULL,
`Quantity_5` int(11) default NULL,
`Drawing` longblob,
`Other_Doc` longblob,
PRIMARY KEY (`Part_Request_Number`),
FULLTEXT KEY `XXXX_part_Number` (`Lutz_Part_Number`,`Customer_Part_Number`,`Description`,`Material_ASTM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

PartVendorPricing SQL

CREATE TABLE IF NOT EXISTS `PartVendorPricing` (
`Part_Request_Number` int(11) NOT NULL,
`Vendor_ID` int(11) NOT NULL default '0',
`Pricing_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`Part_Request_Number`,`Vendor_ID`,`Pricing_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Pricing SQL

CREATE TABLE IF NOT EXISTS `Pricing` (
`Pricing_ID` int(11) NOT NULL auto_increment,
`Pricing_1` decimal(10,4) default NULL,
`Pricing_2` decimal(10,4) default NULL,
`Pricing_3` decimal(10,4) default NULL,
`Pricing_4` decimal(10,4) default NULL,
`Pricing_5` decimal(10,4) default NULL,
`Tooling_Charge` decimal(10,0) default NULL,
`Delivery_Time` mediumtext,
`Part_Request_Number` int(11) default NULL,
`Vendor_ID` int(11) default NULL,
PRIMARY KEY (`Pricing_ID`),
KEY `Part_Request_Number` (`Part_Request_Number`),
KEY `Vendor_ID` (`Vendor_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


Vendor SQL

CREATE TABLE IF NOT EXISTS `Vendor` (
`Vendor_ID` int(11) NOT NULL auto_increment,
`Vendor_Name` varchar(250) default NULL,
`Vendor_Email` varchar(250) default NULL,
`Part_Request_Number` int(11) default NULL,
PRIMARY KEY (`Vendor_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

[shannah]

Your Part_Request_Number columns in the Pricing and Vendor tables are redundant. The PartVendorPricing table effectively relates Parts, Vendors, and Pricings. Having these extra fields causes duplication of data (not good) and confuses Xataface.

Consider removing the Vendor.Part_Request_Number and Pricing.Part_Request_Number fields from your schema.

-Steve