Gossamer Forum
Home : Products : DBMan SQL : Discussion :

easy relational data question

Quote Reply
easy relational data question
Hi all,

I have 3 tables: product, supplier and po. Each product belongs to a supplier. I use the po table to create purchase orders. I set up the po add_form with a dropdown select which is populated with the suppliers. The add_success forwards you to the modify_form to add the products to the po. I have setup 10 items to add to the po. What I need help with is creating dropdown selects for the 10 items that are populated with the products that are owned by the supplier. I used <%Dbsql::Relation::HTML::generate_full_down('product', 'supplier')%>, but it contains all of the products, not just the particular suppliers products.

Please help. Thanks.
Quote Reply
Re: [newbe101] easy relational data question In reply to
OK, I found a thread that has some javascript that really helps:

sub {
return { cats => $DB->table('supplier')->select()->fetchall_hashref };
}

sub {
return {subcats => $DB->table('product')->select()->fetchall_hashref() };
}

<%cat1%>
<%subcat1%>
<script type="text/javascript">
var subcat = new Object();
<%loop subcats%>
subcat[<%ProdID%>] = { name: '<%escape_js name%>', cat: <%SupID%> };
<%endloop%>
function loadSub(cat) {
var subObj = document.getElementById('subcat');
while (subObj.options.length > 0) { subObj.remove(0); }
for (var k in subcat) {
if (subcat[k].cat != cat) continue;
var o = document.createElement('OPTION');
subObj.options.add(o);
o.innerHTML = subcat[k].name;
o.value = k;
}
}
window.onload = function() {
var catObj = document.getElementById('cat');
if (cat.selectedIndex >= 0) {
loadSub(cat.options[cat.selectedIndex].value);
}
}
</script>


<form name="myform" action="db.cgi<%ifnot use_cookie%>?sid=<%session_id%><%endif%>" method="post" <%if enctype%><%enctype%><%endif%>>
<center>
<p>
<select id="cat" name="SupID" onchange="loadSub(this.options[this.selectedIndex].value)">
<option>--------</option>
<%loop cats%><option value="<%SupID%>"><%company%></option><%endloop%>
</select> <br>
<select id="subcat" name="prod1">
</select><br>


My revised question is, how do I get cat to change 2 different select boxes?
Quote Reply
Re: [newbe101] easy relational data question In reply to
Never mind... got my question answered somewhere else... here's the code in case it helps someone else:

<%cat1%>
<%subcat1%>
<%subcat2%>
<script type="text/javascript">
var subcatA = new Object();
<%loop subcats%>
subcatA[<%ProdID%>] = { name: '<%escape_js name%>', cat: <%SupID%> };
<%endloop%>
function loadSub(SelectObj, ArrObj, DestID) {
if (SelectObj.selectedIndex >= 0) {
var cat = SelectObj.options[SelectObj.selectedIndex].value;
var subObj = document.getElementById(DestID);
while (subObj.options.length > 0) { subObj.remove(0); }
for (var k in ArrObj) {
if (ArrObj[k].cat == cat) {
var o = document.createElement('OPTION');
subObj.options.add(o);
o.innerHTML = ArrObj[k].name;
o.value = k;
}
}
}
}
window.onload = function() {
loadSub(document.getElementById('cat'), subcatA, 'subcat');
loadSub(document.getElementById('subcat'), subcatA, 'subcat2');
}
</script>


<form name="myform" action="db.cgi<%ifnot use_cookie%>?sid=<%session_id%><%endif%>" method="post" <%if enctype%><%enctype%><%endif%>>
<center>
<p>
<select id="cat" name="SupID" onchange="loadSub(this, subcatA, 'subcat'); loadSub(this, subcatA, 'subcat2')">
<option>--------</option>
<%loop cats%>
<option value="<%SupID%>">
<%company%>
</option>
<%endloop%>
</select>
<br>
<select id="subcat" name="prod1">
</select>
<BR>
<select id="subcat2" name="prod2">
</select>