Relationship with auto populate

[cantlep]

Hiya, Hoping you can help out with this one

I have two tables..."SiteData" and "BillData". "SiteData" contains various columns. (e.g SiteName, SiteField1 & SiteField2). There is a relationship between the two tables so that when I create a new bill in "BillData", I am presented with some checkboxes that give me an option to add a SiteName from "SiteData". This works fine.

Now, what I'd like to do (in "BillData") is say something like.....

if SiteName = Site1 then when I save the record, also go and get me Site1's SiteField1 & SiteField2 columns and let me use BillData.php to do some arithmetic with them. (I know the arithmetic already works but I'm having to do it with fields that natively exist in "BillData" and I want to be able to do it with fields that exist in "SiteData" based on the SiteName that is chosen.

1) Does that make sense?
2) Is it possible?

Thanks muchly.

Paul

[shannah]

You could use the beforeinsert trigger. Load the associated objects you want to work with and do your arithmetic.

[cantlep]

Cheers Steve,

How do I load the objects? The wiki only gives an example of sending an email using an insert trigger.

Cheers

Paul

[shannah]

I have started a wiki page on the Xataface API. It's bare bones right now, but it shows some examples of loading records from the database and saving them.

http://xataface.com/wiki/Introduction_t ... taface_API

[cantlep]

Hi Steve,

Some decent documentation, cheers. (I've been away so only just got around to doing something with this).

I still can't see how I can return multiple fields from one table to another. I notice the df_get_records_array function get load multiple records of the same type (i.e. people). Can I use the same function for loading multiple fields?

i.e. In BillData I want to create a new bill. When I select the SiteName (in this case, foosite1), use a trigger (after_insert?) to grab me SiteData's foosite field1 and field2 and show them as if they were part of the BillData table.

Thanks

Paul

[shannah]

When you say "show them as if they were part of the BillData table" what do you mean? How do you want to show them? What do you want to do with them?

-Steve

[cantlep]

Hi Steve,

Well. It's as follows:

I have a table called "SiteData". In it are a few columns. Let's say SiteName, Col1 and Col2.

Now, in the "BillData" table (which has a relationship with "SiteData") I want to be able to select a Site from "SiteName" (works fine) and automagically grab SiteData.col1 and SiteData.col2 (for that site) and have them show in list view for "BillData" (so that I can then do some arithmetic with them).

So basically when I view "BillData" I'll see SiteName (as selected), SiteData.col1 and SiteData.col2

Cheers

[shannah]

OK.... you don't want to use an afterInsert trigger or anything like that. Rather, you should graft the relevant information from SiteData onto the BillData table using the __sql__ directive of the BillData table.

e.g. in the BillData fields.ini file

Code: Select all

__sql__ = "select b.*, s.site_name, s.col1, s.col2 from BillData b left join SiteData s on b.Site_ID=s.SiteID"


[cantlep]

Cheers Steve,

I'm probably being stupid but where in the BillData/fields.ini ? I tried it in BillData/BillData.php and it just errored.

Cheers

Paul

[shannah]

BillData/fields.ini
Put it at the beginning of it.

See http://xataface.com/wiki/fields.ini_file

-Steve

[cantlep]

Doesn't seem to do anything: The same page is displayed as before. I'm clearly being a numpty, just can't see where.

BillData/fields.ini

Code: Select all

__sql__ = "select b.*, s.SiteName, s.Eelectric, s.Gas from BillData b left join SiteData s on b.SiteDataID=s.SiteDataID"

[BillDataID]
visibility:list = hidden
visibility:browse = hidden
etc, etc


Not sure if it makes a difference but I have a table called SiteData_BillData that handles the existing relationship.

Code: Select all

[SiteData]
SiteData_BillData.SiteName=SiteData.SiteName
SiteData_BillData.BillDataID="$BillDataID"


[cantlep]

OK, sort of there now but I've not done something right judging by the error. in my BillData/BillData.php I had this

Code: Select all

return "select t.*, datediff(t.SupplyPeriodEnd,t.SupplyPeriodStart) as StandingChargeUnits, t.TotalCostExVAT*t.MPAN1LowerVATPerc/(100)*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/(100)*t.HighBillDataVATRates as VAT, t.TotalCostExVAT*t.MPAN1LowerVATPerc/(100)*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/(100)*t.HighBillDataVATRates + t.TotalCostExVAT AS BillTotal FROM BillData t";


Which works fine.

Now to pull in the stuff from SiteData

Code: Select all

return "select b.*, s.SiteName,s.SiteMPRAQ,s.SiteMPREstAnnGasCons FROM BillData b inner join SiteData_BillData sb on b.BillDataID = sb.BillDataID inner join SiteData s on s.SiteName = sb.SiteName";


which also works fine.

But...

When I join them together.

Code: Select all

return "select t.*, datediff(t.SupplyPeriodEnd,t.SupplyPeriodStart) as StandingChargeUnits, t.TotalCostExVAT*t.MPAN1LowerVATPerc/(100)*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/(100)*t.HighBillDataVATRates as VAT, t.TotalCostExVAT*t.MPAN1LowerVATPerc/(100)*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/(100)*t.HighBillDataVATRates + t.TotalCostExVAT AS BillTotal,s.SiteName,s.SiteMPRAQ,s.SiteMPREstAnnGasCons FROM BillData t inner join SiteData_BillData sb on t.BillDataID = sb.BillDataID inner join SiteData s on s.SiteName = sb.SiteName";


It doesn't work and errors with this (in the browser).

Code: Select all

SELECT COUNT(*) as num FROM `dataface__view_BillData_25218d6cd3d21b266386fb7fc42d58d1` as `BillData` WHERE `Supplier` LIKE CONCAT('%','British Energy','%') AND `SupplierAccountNumber` LIKE CONCAT('%','9999999','%') AND AND `SupplierInvoiceNumber` LIKE CONCAT('%','99999999','%') AND `SupplyPeriodStart` LIKE CONCAT('%','2010-09-07','%') AND `SupplyPeriodEnd` LIKE CONCAT('%','2011-09-07','%') AND `TotalCostExVAT` LIKE CONCAT('%','1000','%') AND `LowBillDataVATRates` LIKE CONCAT('%','0.05','%') AND `HighBillDataVATRates` LIKE CONCAT('%','0.175','%') AND `ValidationStatus` LIKE CONCAT('%','NEW','%') AND `MPAN1LowerVATPerc` LIKE CONCAT('%','50','%') AND `MPAN1HigherVATPerc` LIKE CONCAT('%','50','%')


and this in apache error logs.

Code: Select all

PHP Fatal error:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND `SupplierInvoiceNumber` LIKE CONCAT('%','99999999','%') AND `SupplyPeriodSta' at line 1SELECT COUNT(*) as num FROM `dataface__view_BillData_25218d6cd3d21b266386fb7fc42d58d1` as `BillData` WHERE `Supplier` LIKE CONCAT('%','British Energy','%') AND `SupplierAccountNumber` LIKE CONCAT('%','9999999','%') AND  AND `SupplierInvoiceNumber` LIKE CONCAT('%','99999999','%') AND `SupplyPeriodStart` LIKE CONCAT('%','2010-09-07','%') AND `SupplyPeriodEnd` LIKE CONCAT('%','2011-09-07','%') AND `TotalCostExVAT` LIKE CONCAT('%','1000','%') AND `LowBillDataVATRates` LIKE CONCAT('%','0.05','%') AND `HighBillDataVATRates` LIKE CONCAT('%','0.175','%') AND `ValidationStatus` LIKE CONCAT('%','NEW','%') AND `MPAN1LowerVATPerc` LIKE CONCAT('%','50','%') AND `MPAN1HigherVATPerc` LIKE CONCAT('%','50','%')On line 127 of file /var/www/html/public/dataface/Dataface/QueryTool.php in function printStack in /var/www/html/public/dataface/Dataface/QueryTool.php on line 127, referer: http://www.dogbiscuit.org.uk/index.php?-action=new&-table=BillData


It seems to be the "AND AND" bit it doesn't like and in there I guess should be the SiteName...

Can you assist at all?

Cheers

[cantlep]

Bump - Steve, would you mind having a look at this and seeing if you can assist?

Wondered if it might be better to put that statement into a view and then select * from the view?

[jhenry]

Paul,
I get this error when I change a field in the database that a previous view has been created on and have to recreate the view before I can get it working again. I don't have access to the files I have saved as my laptop just crashed on me but you should be able to search for the mysql commands to recreate the view based upon the sql statement you put in the fields.ini file. Let me know if this is helpful. You have to use the view in the error to make sure you do the right one.

Jason

[jhenry]

Paul,

Just some more info, anytime you use an sql statement in the fields.ini file dataface creates a view so that the data can be used in the application. If a change is made to a field in the database the view errors because it doesn't recognize the new field and it has to be recreated manually. Hope this helps.

Jason