Relationship with auto populate

A place for users and developers of the Xataface to discuss and receive support.

Relationship with auto populate

Postby cantlep » Wed Jun 16, 2010 2:49 pm

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby shannah » Thu Jun 17, 2010 7:33 am

You could use the beforeinsert trigger. Load the associated objects you want to work with and do your arithmetic.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Relationship with auto populate

Postby cantlep » Thu Jun 17, 2010 7:56 am

Cheers Steve,

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

Cheers

Paul
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby shannah » Fri Jun 18, 2010 10:20 am

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
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Relationship with auto populate

Postby cantlep » Tue Jul 06, 2010 3:08 am

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby shannah » Tue Jul 06, 2010 9:43 am

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
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Relationship with auto populate

Postby cantlep » Tue Jul 06, 2010 10:33 am

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby shannah » Tue Jul 06, 2010 11:30 am

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"
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Relationship with auto populate

Postby cantlep » Tue Jul 06, 2010 12:27 pm

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby shannah » Tue Jul 06, 2010 1:11 pm

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

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

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Relationship with auto populate

Postby cantlep » Tue Jul 06, 2010 1:20 pm

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
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby cantlep » Fri Jul 09, 2010 8:58 am

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
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby cantlep » Fri Jul 16, 2010 7:17 am

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?
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Relationship with auto populate

Postby jhenry » Fri Jul 23, 2010 7:49 am

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
Do not mistake understanding for realization, and do not mistake realization for liberation....Tibetan Saying.
jhenry
 
Posts: 58
Joined: Sun Jul 12, 2009 1:20 pm
Location: Florida

Re: Relationship with auto populate

Postby jhenry » Fri Jul 23, 2010 7:54 am

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
Do not mistake understanding for realization, and do not mistake realization for liberation....Tibetan Saying.
jhenry
 
Posts: 58
Joined: Sun Jul 12, 2009 1:20 pm
Location: Florida

Next

Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 34 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved