Hi

I have a table of properties  that uses a secondary table with foreign key to populate street names.There are numerous properties on numerous street so we have a many to many relationship. 

Setting up the lookup table  for a street name in a new property record is just fine.However the user wants a view  to filter by street and to do this it would seem either

1. the street field in the property table contains the street name . This then breaks the referential integrity of the lookup table - ie if the spelling is changed in the look up table , it would not reflect in the property table  or

2. the street field holds the street ID and you have to search on the ID number which is not good 

Is there something I am missing

Secondly I am having problems creating a view with the main table - say properties  pulling data from a  street table and property type table . 

I tried creating a new SQL search in the editor but my script seemed to get overwritten  when I tried to create the table. 

Otherwise the plugin is just great

Cheers

Richard

Further to above comment on sql script attached two images - One show the script the second shows the rendered query

Just some meore detail on getting the SQL script for three table. Attached image shows setting for getting a view of Masterindex,strett and Full_name from three tables.

The generated script was 

SELECT Progress_Tracker_1.`Masterindex`,
       Streets.`Street`,
       Volunteer.`Full_Name`
FROM Streets
  INNER JOIN Volunteer
     ON Progress_Tracker_1.`VID` = Volunteer.`ID`
  INNER JOIN Progress_Tracker_1
     ON Streets.`ID` = Progress_Tracker_1.`StreetID`

and generated an error as Progress_Tracker_1 was undefined

This did not run but this does

SELECT Progress_Tracker_1.`Masterindex`,
       Streets.`Street`,
       Volunteer.`Full_Name`
FROM Progress_Tracker_1
  INNER JOIN Volunteer
     ON Progress_Tracker_1.VID = Volunteer.ID
  INNER JOIN Streets
     ON Streets.`ID` = Progress_Tracker_1.`StreetID`

Help here would be gratefully appreciated

Cheers

Richard

Apologies - forgot the image !

Hi Bogdan

Many thanks for that - I should have thought it through myself !! Old age  I guess !.

However I do still have a slight issue. I have created a view from a master table and foreign key tables It imports into WPtables fine however I have a problem with updating.I only wish to update a field from the master table. I can do this with no problem in the excel type editor, ( attached image shows master table fields-grey, foreign fields Yellow and Green, editing Textrec field only)   however when I switch to standard editor I get the attached error saying cannot join more than one table when I apply udates. It would seem the two editors generate different scripts !.  Does the excel editor just update the cell whereas the standard editor tries to update the row even when there are no changes to fields? This will give me a problem as I would like some users to just update using the web page view. Can I get the excel editor to display on the webpage ? Any ideas??

Cheers

Richard

Thanks Bogdan

• MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.

I understood this to mean that although a view came from two tables, one could edit one of them  - but that doesn't seem to be the case ! - Had read all the notes but I guess had misunderstood  - I had assumed a view would be treated as one table !

Anyway - many thanks and we will work around it somehow. How do you manage an order situation where the order table will require to pull a vendor and an order item type say ? I an sure many of your users would have that type of configuration

Thanks again

Richard

Hi aqain Bogdan

I caanot see where to replicate your screen dump. Where do I find that option box?? !

Cheers

Richard

Yeh - You learn something new very day !!!! - Thanks 

I am going to have to play about !!

Many thanks

Richard