Getting data from a secondary table and saving to variables

Classic Servoy
1

Hope I can explain this clearly enough:

1.) Primary table contains an order number.

2.) Either automatically or at the click of a button, I want to go to another table and get the three records that correspond to the order number. This is what I am doing so far:

function get_totals() {
// get the order number
orderid = forms.orders.orders_id;

// locate records from secondary table
var totalvalues = foundset.addFoundSetFilterParam(‘orders_id’,‘=’,orderid);
foundset.loadAllRecords();

subtotal = amount;

// goto next record
var nextrec = foundset.getSelectedIndex();
foundset.setSelectedIndex(nextrec+1);

shipping = amount;

// goto next record
var nextrec = foundset.getSelectedIndex();
foundset.setSelectedIndex(nextrec+1);

tax = amount;
}

3.) …and then display the information. The above works OK for the first record in the primary table, but doesn’t for subsequent records – the variables literally all come up showing blank.

This seems like a fairly basic concept that would be used often in a programming situation, but I am surely missing it somewhere!

2

rsthomas1:
// locate records from secondary table
var totalvalues = foundset.addFoundSetFilterParam(‘orders_id’,‘=’,orderid);
foundset.loadAllRecords();

If I’m not mistaken, foundset filters do add up creating an ‘AND’ query. (you should be able to see this in the performance page of the servoy-admin pages)

So if you set the foundset filter for the first record it’s all going well, you just filter all records where order_id = ‘0001’
Doing this for the 2nd record adds a filter, for example where order_id = ‘0002’;

after this a load all records will result in a query saying: show me all records where order_id = ‘0001’ AND order_id = ‘0002’

bet there are not many records that have these requirements.

Solution:

  1. do name the foundset filters and remove them after use
  2. why using foundset filters? (this is what I asked myself in the first place) IMHO, creating a relation between the two tables would be much easier.
3

Thanks a bunch for the tip about removing the foundset. It works now. I knew it was something simple.

As for the thought about using a relation, I tried that first but it seemed that it was starting at the last record in the relation instead of at the top (in this case the tax amount instead of subtotal), and then I didn’t know what to do…what am I missing with this one?

4

This could be a problem with filtering as well.
what if the records are created in a different order, or the middle one is deleted and created afterwards?

I’d say you need a column which indicates the function (could be an integer)
Then just sort and do the thing…

5

Naturally, the relation itself is based on the order number. The initialSort property is based on the order number and an internal sort_order field.

When I look at the Totals form for an order in Table View they are displayed in proper order.

Anyway, I am correct in assuming that the “record pointer” should be only at the first record matching the relation and not somehow select them all at once?

How do I then go from record to record – with the

var nextrec = foundset.getSelectedIndex();
foundset.setSelectedIndex(nextrec+1);

commands? That doesn’t sound right since I haven’t really established a foundset with just the relation, or have I?

Learning new languages is so much fun(?), from Basic in 1984 to dBASE III to Foxpro to PHP, and now Servoy!

6

rsthomas1:
That doesn’t sound right since I haven’t really established a foundset with just the relation, or have I?

even something as forms.orders.orders_to_orderdetails will return you a foundset object which you can easily iterate.

var _myFoundset = forms.orders.orders_to_orderdetails;

for(i = 1; i <= _myFoundset.getSize() ; i++)
{
      var _myRec = _myFoundset.getRecord(i);
      var _myAmount = _myRec.amount; 
}

The code above does everything ‘in memory’ so no switching of forms is involved.
The ‘var _myAmount = _myRec.amount;’ line just illustrates how to read a dataprovider value from a record object.

Iterating the foundset in memory is much faster then ‘onScreen’ so if there’s no need to do it ‘onScreen’ don’t do it…