Dynamic Valuelist lookup (solved)

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

Dynamic Valuelist lookup (solved)

Postby casanovax7 » Wed Sep 05, 2012 4:03 pm

First, I want to say that Xataface is perhaps the single greatest tool I could imagine using for the data collection and sorting that I am doing, and I want to express my sincere thanks for such a powerful and easy-to-use piece of software.

Ok, so, my dilemna right now:

I have two tables, a Vendor table, and an Image table.

In the Vendor table, I have AccountID, LocationID, and LocationName. In the Image table I have ImageID, AccountID, and LocationID. Each account will have many locations, but locations are unique. Each ImageID can be linked to multiple LocationIDs (also, right now the AccountID for the image field is already filled out properly).

My goal is, for the Image table, to have a valuelist that populates with the possible LocationNames based on the already filled in AccountIDs, and enters the LocationID(s) into the database. The main thing is that the valuelist needs to be dynamic, because people are constantly adding new location names.

Could someone give me a start? Anything you can offer would be a great help. Thanks!
Last edited by casanovax7 on Wed Sep 05, 2012 7:55 pm, edited 1 time in total.
casanovax7
 
Posts: 20
Joined: Wed Sep 05, 2012 3:36 pm

Re: Dynamic Valuelist based

Postby casanovax7 » Wed Sep 05, 2012 7:25 pm

I think I figured it out, just need to double-check entries and test it a bit. Here is the code I used to get this to work. If someone more knowledgeable than me happens to see this, please do critique my code, just in case there is anything else I can learn.


For reference for any total noobs like I was, I'll briefly explain what I did. Inside my image.php delegate class file (the delegate class file for my Image table), I placed the following:

Code: Select all
function valuelist__locationtable(){

    $app =& Dataface_Application::getInstance();
    $query =& $app->getQuery();
   
    $imgrecord =& $app->getRecord();
    $vend =& $imgrecord->getValue('AccountID');
    $sql = ("select LocationID, LocationName from Vendor where AccountID='$vend'");
    $res = mysql_query($sql);
    $out = array();
    while ($row = mysql_fetch_assoc($res) ) $out[$row['LocationID']] = $row['LocationName'];
    return $out;
}


Then in my fields.ini file for the Image table:

Code: Select all
[LocationID]
widget:label = "Location within the Venue"
widget:type = checkbox
vocabulary = locationtable


This gives a me a checklist based on Locations for a specific Vendor, which then saves the LocationID(s) into the database based on which LocationName(s) are checked.

Again, comments/critiques/questions welcome, thanks!
casanovax7
 
Posts: 20
Joined: Wed Sep 05, 2012 3:36 pm

Re: Dynamic Valuelist lookup (solved)

Postby casanovax7 » Thu Oct 04, 2012 2:18 pm

This was "solved" but I eventually figured out that I had not quite done everything correctly. In the case that new records needed to be created, the code I posted above gives a blank screen and logs an error when saving a new record. Here is the code I used that allows me to only run the valuelist query when dealing with an existing record.

Just for clarification for anyone wondering what exactly I was doing (in case this might help someone else down the line): I set up Xataface as a way to collect data and images for vendors that my company uses. I have two tables, one for vendors (actually, for specific locations that a vendor has), and one for images (which are then related). When an image is uploaded it is required for it to be assigned a vendor. Then, for this part of it, I needed to be able to assign an image to a specific vendor location. And due to the difficulties of making valuelists dynamic (until Xataface 2.0 that is), I felt it was easier to simply only query the database for the valuelist when an image record already exists (when the vendor id field was already filled in; so anytime after an image has been uploaded). Thus, this code gives me a drop-down valuelist only for images that have already been uploaded (in "edit" view):

Code: Select all
function valuelist__locationtable(){
    $app =& Dataface_Application::getInstance();
    $query =& $app->getQuery();
    $sql = 'None';
    $action = $query['-action'];
    switch ($action){
        case 'edit':         
            $imgrecord =& $app->getRecord();
            $vend =& $imgrecord->getValue('img_vendor');
            if ( $imgrecord){
                    $sql = "select SF_ID, Location_Name from vend_01 where Vendor_Account__c='$vend' order by Location_Name";
                }
            break;
    }
    $res = mysql_query($sql);
    $out = array();
    while ($row = mysql_fetch_assoc($res) ) $out[$row['SF_ID']] = $row['Location_Name'];
    return $out;
}


Thanks again to Steve for creating Xataface!
casanovax7
 
Posts: 20
Joined: Wed Sep 05, 2012 3:36 pm


Return to Xataface Users

Who is online

Users browsing this forum: Google [Bot] and 39 guests

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