Ebase Tables - session tables

[alexmcclune]

Hi,

I am trying something I haven't tried before and would like some advice. I am building a sort of "shopping cart" idea and am struggling with a few concepts. I have formatted this so the questions are in red and hopefully easier to answer:

Rather than allowing 1 payment for 1 product as is currently the case I am hoping to introduce a shopping cart using a table which isn't backed by a resource in Ebase (lets call it a session table). So ultimately I have 1 database backed resource, "PRODUCTS" which is displayed as a repeater control, and 1 unbacked table "ORDER" which is displayed as a table :

Is it sensible/feasible and possible to use ebase tables for this?

I have already built some of the functionality and whilst some of it works I am now struggling with a few extra features (all of which are caused by the fact the table isn't backed by a resource):

How can I refresh the table when a product is added? I know the products are added as I can see it in the log and if I add more than 2 the screen does update. However I can't get it to refresh on the first product.


Is it possible to perform calculations on the column's? (for example to count the "charge" column to display a total)


How can I compare column values between the database backed resource and the unbacked resource? Ultimately to hide/unhide controls on the repater control

Really hope that makes sense! At the end of all this I will have to build a HTTP POST URL to call the payment interface....

EDIT = I think I know why the table isn't refreshing on the first insert. It is something to do with the first insert only containing null values, my code must be wrong even though if I log the values they are populated - it is currently simply "tables.PRODUCTS.item.value = tables.ORDER.item.value" for the various columnsetc followed by "tables.ORDER.insertRow()"..Will pick this up next week.

[Jon]

Alex,

Yes you can use Ebase tables for your application. There isn't anything particularly magic about Ebase tables - they are just rows of data containing columns - that can optionally be fetched and updated using a database. You can do all the usual things you expect - insert rows, delete rows, loop through rows etc.

It's a bit difficult to answer all your questions without seeing the application, but here's an attempt:

Q: How can I refresh the table when a product is added?
A: Just issue a fetchTable - this assumes the table is backed by a Database Resource

Q: How can I compare column values between the database backed resource and the unbacked resource?
A: The Javascript API gives you a number of methods that can help. You can loop through the rows of each table comparing values as you need to , or you can use the findRow() method to search a table for certain column values.

One thing I noticed, is that to insert a row you need to issue insertRow() first, then set the values e.g.

Code: Select all

var prodTab = tables.PRODUCTS;
prodTab.insertRow();
prodTab.PRODUCT_ID.value = xxx;
// etc

Regards
Jon

[Dave]

Alex - there's a complete timesheet sample that you could download from the Ebase web site:

http://www.ebaseftp.com/download/sample ... esheet.zip

Obviously not quite the same thing as a shopping cart but there are similarities and it does demonstrate answers to many of the questions that you raise, I think.

Regards,
Dave

[alexmcclune]

Thanks for your help - I have got quite far, it is basically doing everything I need with 2 exceptions - I found the table scripting page in the help menu and it was useful.

1 - Need to offer a "quantity" column in shopping cart which varies depending on the product (not worried about this as I have a theory about how to do it)

2 - I am usingform.callUrl([pay_url], parms, form.HTTP_PROTOCOL_POST); to call the URL but the provider requires a URL to be built with partially fixed variables but also "Lines"for each product prefixed as "Line_1", then "Line_2" etc. My problem is, I don't know how to amend the variable for each record, so "Line_1" is for the first record, "Line_2" for the second and so forth. I have tested the url with 1 "Line" hardcoded as "Line_1" and it works as expected. I obviously cannot use "parms.Line_" + iterator + ' = ' + ref + '|'...but doesn't work. Therefore as I needed to execute code I understand eval(x) is the solution (I am aware of it's "issues" so please let me know if there is a better way) as I needed to build the correct string "parms.line_" + iterator + ' = ' + ref + '|' + 'code'...unfortunately the eval always reports parms.line_1 = 0 and I don't know why yet.

Code: Select all

var parms = {};
  parms.ID = ID;
  parms.Total = fields.FRM_PAY_SUBTOTAL.value;
  parms.ReturnURL = form.getReturnUrl();

var rowsCart = tables.SHOPPING_CART.getRows();
var iterator = 0;

while (rowsCart.next()){
  iterator += 1;
  var Ref = tables.SHOPPING_CART.REF.value;
  var Code = tables.SHOPPING_CART.PAYMENT_CODE.value;
  var Total = tables.WASTE_SHOPPING_CART.COST.value * tables.SHOPPING_CART.QUANTITY.value;
   var VatCode = tables.SHOPPING_CART.VAT_CODE.value;
  if (tables.SHOPPING_CART.PAYMENT_REF.value){
	var code = 'parms.Line_' + iterator + ' = Ref + '|' + Code + '|' + Total + '|' + VatCode ;
	eval(code);
  }
}
form.callUrl([pay_url], parms, form.HTTP_PROTOCOL_POST);

So in theory with 3 records in the Cart there should be a few fixed variables in parms followed by 3 line_1 = detail|detail|detail;line_2 = detail|detail|detail;line_3=detail|detail|detail.

Really hope that makes sense, thanks for the help. Let me know if there are any other obvious mistakes (I edited my code to be more generic so there maybe some typo's).

[Jon]

I think you can do this without using eval. In general, you can add a variably named property to a Javascript object using square brackets [], like this:

Code: Select all

var obj = {};
var key = "abc";
var value = 123;
obj[key] = value;

This creates a property of obj named abc with value 123.

Following this through to your example, you should be able to do something like this:

Code: Select all

var parms = {};
while (rowsCart.next()){
  iterator += 1;
  var Ref = tables.SHOPPING_CART.REF.value;
  var Code = tables.SHOPPING_CART.PAYMENT_CODE.value;
  var Total = tables.WASTE_SHOPPING_CART.COST.value * tables.SHOPPING_CART.QUANTITY.value;
  var VatCode = tables.SHOPPING_CART.VAT_CODE.value;
  var parmName = "Line_" + iterator;
  var parmValue = ....;
  parms[parmName] = parmValue;
}

[Steve James]

Hi Jon, related to this should data in a local table persist even if I change the content in the original table?

In the below example I was finding that the content of tmpTable changed once I changed the content of the original DBTABLE.

It's not a full example but I hope it shows the essence.
I have a table of requestors by site. If a requestor withdraws from a site I need to update the individual record but also cascade the queue positions for the other requestors in the list after the withdrawer.

eg

Code: Select all

fields.SQLWHERE = 'requestorid = 5';
tables.DBTABLE.fetchtable();

var tmpTable = tables.DBTABLE;

var RequestorRows = tmpTable.getrows();

while (RequestorRows.next())
{
    log(tmpTable.ID.value);
    if (tmpTable.STATUS.value == 'withdrawn')
      {
         var siteid = tmpTable.SITEID.value;
         var qpos =  tmpTable.QUEUEPOSITION.value;
         fields.SQLWHERE.value = 'queuepos >= ' + qpos + ' and siteid=' + siteid;
         var siteRows = tables.DBTABLE.fetchtable();
         while (siteRows.next())
          {
               if (tables.DBTABLE.QUEUEPOSITION.value == qpos)
               {
                     tables.DBTABLE.QUEUEPOSITION.value = -1;
                }
                else
                {
                     tables.DBTABLE.QUEUEPOSITION.value = tables.DBTABLE.QUEUEPOSITION.value - 1;
                }
          }
          tables.DBTABLE.updateTable();
       }
}

I've ended up creating a second db resource which is a copy of DBTABLE.

Thanks

Code: Select all

[Jon]

Steve,

In your example, tmpTable and DBTABLE are actually the same thing i.e. they are pointing to the same underlying table within Ebase. Issuing a fetchTable within a loop through the rows is probably going to go wrong at some point, so I'm not surprised that you have problems.

You could set up a second table not backed by a Database Resource with the same columns as the first table, then copy the data from one table to another using copyTable(). Then you can treat the two tables as independent entities. I think this would work OK, mind you it does mean duplicating all the row data in memory.

Regards
Jon

[Steve James]

Thanks Jon, so in effect the js variable tmpTable is a link to the table rather than a copy of it.

I've ended up doing as you say (actually 3 tables) as I have a
1 - before changes copy
2 - active table
3 - update queue position copy (based on what the actual changes are between 1 and 2).

Thanks
Steve