SQL based Value Lists drawing upon multiple fields/tables?

[eoneon123]

I know how to create conditional value lists using sql based on a single field but I'm wondering if it's possible to draw upon alternative fields given a set of conditions set out in a Case function. I know how to do this when drawing upon values from a single field but I'm struggling with how to incorporate multiple fields, which live in different tables. 

 

Any ideas? 

In case that's convoluted, I'm using a single field in a portal and I want the value list to draw values from Field1, Table1 under one set of criteria set out in a Case function, while drawing upon Field2, Table2 under another set of criteria. Hope that's more clear. 

 

Thanks in advance!

[dansmith65]

After you gather value list values via sql, what do you do with them?  Set them to a field that has a value list based on it?

 

If you are using sql already, then it should be a simple If statement:
If (

    yourCondition ;

    sql select from Table1::Field1 ;

    else sql select from Table2::Field2

)

[eoneon123]

Yup, that's what I was doing. I think there might be something wrong with both my Case function and the way I have the relationship set up. The second condition doesn't seem to work on its own, while the first one breaks down when (my intended and probably faulty) second condition is set anywhere in the portal.

 

I'm trying to get this to work from a portal (Table1) where I first create a record by way of a button that sets a field to condition "A" or "B". Based on this condition, I want the value list to draw values from either Table2::Field2 or Table3::Field3. The calc field that generates the SQL based conditions lives on Table3 and is related to Table1 (the portal table). I'm not an expert with FMP or SQL so I can't really even remember the logic of this set up, just that it worked with a single SQL condition after some trial and error. 

 

I have some ideas about rearranging the Table structure to make this work (namely creating a super table that holds all the values) but I guess I just wanted to know if there was a possibility of this technique working. 

 

Happy thanksgiving, by the way. 

[eoneon123]

I figured this out. My relationships were messed up and I needed to set a couple of variables to capture the secondary values I was looking for. Thanks again.