Error Message with Delegate

[tnorbut13]

Bump

[shannah]

Looks like there is a problem in your Vendor_ID__pushValue() method:

Code: Select all

$vendorName = $element->getValue();
// Get the associated employer id
$vendorRecord = df_get_record('Vendor', array('Vendor_Name'=>$vendorName));


$element->getValue() will return the ID (possibly an array of ids) of a vender not a name.

[tnorbut13]

Ok. Based on everything I've posted, though, how can I accomplish the goal of populating Name in the select but transferring ID to the record?

[shannah]

The problem was that your select list was being populated with (id,name) pairs and would store the id. Then in your pushValue() function you were expecting the name - but it was giving you the id. Similarly your pullValue function was giving you the name but xataface expected an id.

I'm not sure why you can't just get rid of the pushValue/pullValue methods and use the field as defined with the valuelist. By default the select list shows the names but stores the ids.

[tnorbut13]

Steve,
I really appreciate your time on this. I know it must be a little frustrating dealing with issues like this, but it shows how much you care about this product.

Ok. I eleminated all of the delegate classes from partvendorpricing and just put in a fields.ini and a valuelist.ini but still no dice. I get no select field to choose from on the record.

Here is the Fields.ini:

[VendorID]
widget:type = select
vocabulary = Vendors

Here is the valuelist.ini:

[Vendors]
__sql__ = "select Vendor_ID, Vendor_Name from Vendor ORDER BY Vendor_Name"

Looks like it should work, but it doesn't.

[tnorbut13]

Ok. I think I have a lead on why this might be happening, but I still don't know a work around.

To get to my PartVendorPricing record from my Part screen, I have the following relationship:

[VendorPricing]
PartVendorPricing.Part_Request_Number="$Part_Request_Number"
Pricing.Pricing_ID=PartVendorPricing.Pricing_ID

However, this is where I don't get a select option for Vendor as I have the field definition in another table called PartVendorPricing. The relationship for PartVendorPricing is:

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

This is where I can't get the work around figured out. I need to relate the pricing record to both the Vendor and to the Part. What am I missing?

For further consideration, I have put the valuelist and field ini in the base of the application. When I do this, the select does work on the VendorPricing screen (as it should). However, when the record is saved, it saves the name of the vendor, not the ID.

[tnorbut13]

I'm beginning to get an idea of what to do now, but still need some help with a SQL query.

It's pretty clear that I'm going to have to do a SQL statement with a join to get all 3 tables set up together on the same tab for use. Here's what I have for a statement (don't laugh, I know it isn't good):

[VendorPricing]
__sql__ = "SELECT Vendor.Vendor_ID, Vendor.Vendor_Name, pvp.*, p.* FROM PartVendorPricing pvp left join Pricing p on pvp.Pricing_ID = p.Pricing_ID AND pvp.Vendor_ID = Vendor.'$Vendor_ID' AND pvp.Part_Request_Number = '$Part_Request_Number'"

And I get this error:

Fatal error: [pear_error: message="Parse error: Unexpected clause on line 1 SELECT Vendor.Vendor_ID, Vendor.Vendor_Name, pvp.*, p.* FROM PartVendorPricing pvp left join Pricing p on pvp.Pricing_ID = p.Pricing_ID AND pvp.Vendor_ID = Vendor.'$Vendor_ID' AND pvp.Part_Request_Number = '$Part_Request_Number' ^ found: "$Vendor_ID"" code=0 mode=return level=notice prefix="" info=""]
On line 228 of file /var/www/xataface/Dataface/Relationship.php in function printStackTrace()
On line 102 of file /var/www/xataface/Dataface/Relationship.php in function _init(array(SELECT Vendor.Vendor_ID, Vendor.Vendor_Name, pvp.*, p.* FROM PartVendorPricing pvp left join P)
On line 1486 of file /var/www/xataface/Dataface/Table.php in function Dataface_Relationship(Part,VendorPricing,array(SELECT Vendor.Vendor_ID, Vendor.Vendor_Name, pvp.*, p.* FROM PartVendorPricing pvp left in /var/www/xataface/Dataface/Relationship.php on line 228

Need to get from point A to point B here. How can I adjust the SQL statement to JOIN the 3 Tables together?

[shannah]

I don't have the full picture but I'm getting the feeling that this database is not normalized. You are trying to create a relationship between which two tables? It looks like it is going from
Part to PartVendorPricing. Is this correct?
If so, just leave everyone else out of the mix. And you don't care about Vendor_ID at all for this relationship because you'll want pricing for all vendors for this part.

What is the primary key of the Part table?
What is the primary key of the PartVendorPricing table?
What is the foreign key of the PartVendorPricing table on which you are joining it to the Part table?

The answer to these questions will determine exactly how you need to define the relationship. All other things should not be part of the relationship. They can be handled elsewhere by either adding calculated fields to tables or using valuelists cleverly.

[tnorbut13]

Primary Key for Part is Part_Request_Number
Primary Key for PartVendorPricing is made up of 3 foreign keys (Pricing_ID, Vendor_ID, Part_Request_Number)
No Foreign Key

[tnorbut13]

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;

[shannah]

OK. So it looks like the relationship is actually between the Part table and the Pricing table using the PartVendorPricing table as a join table.

So we would have:

Code: Select all

[VendorPricing]
PartVendorPricing.Part_Request_Number="$Part_Request_Number"
Pricing.Pricing_ID = PartVendorPricing.Pricing_ID


Then you would set the widget:type of the Vendor_ID field (in the PartVendorPricing table) to be a select widget using a valuelist of all of the vendors.

[tnorbut13]

And this relationship is in the PartVendorPricing, correct?

[shannah]

No. If it is going from the Part table to the Pricing table, it would be in the Part table's relationships.ini file.

[tnorbut13]

And the Valuelist entries should be in the Part table too, correct?

[tnorbut13]

Ok. This gets me almost there....but

Now the Part_Request_Number is blank. The Vendor select is there (great) but nothing in Part_Request_Number, which should be the current record, correct?