Active link table example with argument passing problem

[dordije]

Hi all,

as I did not find proper solution for my problem in thread named Active link tables, sets and Record list-combo box, I am sending a small example.

Please, I ask anyone who works with Active link tables and maybe
with Firebird databases to answer on this question:

I work with Alpha Five V9 Platinum edition.
Why A5 prompts me for argument value when opening form frmFIRCARS ?

As attachement I am sending:
- APPTEST2.ZIP (this is APPTEST.FDB Firebird 2.1 database, dialect 3)
- APPTEST.ZIP (this is application files)

Create folder named AAA (or wih some other name). In that drive copy these files.
Extract all files from APPTEST.ZIP.

Create System DSN ODBC data source named APPTEST. It must point to APPTEST.FDB.
Open this application (APPTEST) in Alpha Five.

I have three tables:

Accfir (table of firms)
Cartyp (table of car types, where each firm has its one list of car types. This is mandatory,
I can not make table this on list of car type for all firms).
Fircar (table of cars which one firm bought)

I have one set:

SetFirmCars (this set links Fircar table as parent and Cartyp as lookup table).

See link definitions for tables Cartyp and Fircar. They have WHERE clauses based on arguments.

When starting application, I first open form frmFIRMSELECT. Please select firm named
SAMPLE FIRM 2. When I select firm 2 I put its ID value in global variable pFIRREC.

After that, it opens form frmFIRCARS (based on set), prompting for argument value.

Why, because I open that form with command FORM.view("frmFIRCARS","","","","",mArgs)
where I pass value for argument.
Please see OnPush event code on Select button in frmFIRMSELECT form.

If it is impossible to solve this with A5 designed forms, is it possible to solve it with
xBasic created dialogs ? I need small example, but for situation I have in my case.

Thanks,

Dordije.

[Peter.Wayne]

Re: Active link table example with argument passing problem

Dordije,
I don't have Firebird so I can't test your application -- I know Firebird is free, but so is MySQL, and I think MySQL is better documented. However, can you just tell me how you are passing the arguments in mArgs? Did you use
dim mArgs as sql::arguments
mArgs.set("argument_name",argument_value)

- Peter

[dordije]

Re: Active link table example with argument passing problem

Hi Peter,

thanks for reply.

Here is how I pass arguments to form frm FIRCARS:

OPTION STRICT
DIM mArgs As SQL::Arguments
ON ERROR GOTO Error_Handler
pFIRREC:=ACCFIR->FIRREC ' selected firm put in global var
pFIRNAM:=ACCFIR->FIRNAM ' firm name
'
mArgs.Add("aFIRREC",pFIRREC)
' arguments in both tables have same name aFIRREC
'
FORM.view("frmFIRCARS","","","","",mArgs)
'
END
Error_Handler:
script_play("Error_Handler_01")

I think that problem is not caused because of Firebird. I think it will happen also in MySQL or any other database.

You can create two tables in MySQL both to have WHERE clause with arguments in its SELECT statements. One table has to be parent and other some lookup table. In parent table you must have ID field of lokup table.

Then make set on that two tables. Then make form based on set, and put some fields of parent table on it. Also put on it IDfield of lookup table.

In properties change type of that IDfield to be Record list-combo box. Try to open that form without A5 prompting you for arguments. That is my problem !

Thanks again,

Dordije.

[Peter.Wayne]

Re: Active link table example with argument passing problem

Dordije,
I have your forms and made MySQL tables to match, but I can't figure out what you're trying to do. Your frmFIRCARS is based on a set but all the fields are found in the child table. Can you explain what you're trying to do? It might be easier if you try it first with regular Alpha Five dbf tables. If you can get it to work with dbf tables then you should be able to get it to work with active link tables. But I'm having trouble figuring out what you're doing, or attempting to do.
- Peter

[dordije]

Re: Active link table example with argument passing problem

Hi Peter,

Sorry, I am really messed with my problem and I sent not enough informations about it. Thats why I sent example, hoping that someone works with Firebird.

On my form all fields are from parent table, not from child table. In set I have parent table and one child table only as lookup table for name of car type.

In parent table among other fields I have also field which is ID field for row in child table (foreign key in parent table - ID for car type in CARTYP table).

Well, on form I put a few fields from parent table and among them is also foreign key field. Then I changed properties of that foreign key field. I changed its Control type (in Setup tab) from Type-in field to Record list-Combo box. Then in Choices tab I select from table (it is child table name), and what is return value (it is ID field of row in child table).

In this way I have combo box which serves user to see and select car type by name and other descriptive fields. Record list-Combo box is much better then plain Combo box control.

But when I open this form A5 prompts me for argument value. It appears that A5 uses passed argument value only for parent table. When opening form (no prompt for argument value). When A5 starts to init and open described combo box, A5 prompts for argument value. Question is can I suppress this prompt and pass argument value for both tables in set ?

Now I will try to explain what I intend to do:

I have one table which have data about several firms. In my example it is table ACCFIR. For each firm I must read and write data about its several entities (that can be articles, cars, warehouses etc. - for this example I choosed cars, just as example).

Strong request for me is that for each firm's entity I must have separate list of entity instances and entities types: for example each firm must have its own list of articles and article types, or list of warehouses, or list of cars and car types. Well, I created one table, say CARTYP in which I have ID for car type but also ID for firm. I can not have one table with all possible types of cars, and then user chooses through whole table. For user I must list only car types which firm has in its possesion.

Also I created one table FIRCAR in which I save data about cars for all firms, so in it I must have ID field of that table row, ID field of firm which bought that car and ID field of car type, and also date of buying that car in that firm.

That is why I must have WHERE clause in SELECT statements for tables FIRCAR and CARTYP.

So I created set with FIRCAR as parent table and CARTYP as child table. In this example parent table serves me to enter data when selected firm bought particular car. When I want to enter data about bought car, user enters buying date and selects car type from combo box (Record list-Combo box).

In the begining of application I have one form for selecting firm (frmFIRSELECT). When user selects firm I put firms ID and name in global vars. Then I open form based on set to enter or modify data obout cars of selected firm (frmFIRCARS).

I am aware of fact that many people have many ways to organize its database, but I choosed this way. So I must have two tables with SELECT statement like this: SELECT * from FIRCAR WHERE FIRREC = :aFIRREC, or SELECT * from CARTYP WHERE FIRREC = :aFIRREC.

I hope this will help you to recreate small example and verify am I wrong or not.

Thanks in advance,

Dordije.

[Peter.Wayne]

Re: Active link table example with argument passing problem

Hi Dordije.
First, let me say although I've used active link tables, I try to stay away from sets as much as possible. I try to restrict all data entry to one-table forms or Xdialogs. That's a personal preference. Obviously, there are times when it's absolutely necessary to use a set.

I think I understand your data structure. You have implemented a typical many-to-many data structure with an intermediate lookup table.

However, I think you don't quite realize how sets work in Alpha Five. You don't have to repeat the WHERE clause in the child table -- it should inherit that from the parent table in a one-to-many relationship.
You also should not need the WHERE clauses in the definitions of the set or of the active link tables. The WHERE clauses should be automatically provided by Alpha Five when you open the forms with a query, e.g.,
f=form.viewQueried("someForm",somefilter,someorder)

That's why I suggest you first see how to do this with Alpha Five native dbf tables, and then once you can do that successfully, you should be able to migrate to Firebird.

[dordije]

Re: Active link table example with argument passing problem

Hi Peter,


About WHERE clauses in active link tables and sets:

On Alpha Software's web page with title "Whats's new in alpha five version 9" you can find text about active link tables. In the begining of that text A5 says:

"Because of this, an application that is built on active-link tables should always ensure that there is a suitable WHERE clause in the SQL statement for the active-link table. Typically, this WHERE clause might include an argument so that you can prompt for a value, or pass a value in programmatically, when the table is opened."

I really agree with this. If I do not have WHERE clause, A5 must operate on all data in table. I think that in case of active link tables filter do not reduce data that must be read from database (server). I think that in case of filter A5 reads all data in local cash and after that only reduces what you will
see in browse or form. I think that filter operates on local cash. But when you have WHERE clause, A5 reads only data that satisfies WHERE clause not whole table. On small tables we do not have some big advantage, but with big tables we have.

As you know when we create active link table we must define SELECT, UPDATE, INSERT and DELETE statements. And I defined SELECT statements for my two tables CARTYP and FIRCAR using WHERE clause because I need it.

When I create set I choose tables which will constitute set, define table which will be parent table and tables which will be child tables. I do not define SELECT statements again. SELECT statements and its WHERE caluses comes with tables which participate in set.

In my set, child table is not say "classic" child table as in example with Order and Order items (one-to-many). It is child in sence of lookup table. For one row in parent table I have one value for row in child table. I only want to choose that value using Record list-Combo box, not type in ID value.

It is simillar to situation when in Order item we want to choose product with name of product. Then in that column we make some sort of combo box and list names of products to choose product. I thing that also in this example A5 will prompt for argument if Order, Order items and Product tables have WHERE clause in its SELECT statements.

I understand that "problem" triggers fact that I have both tables with WHERE clause, but I need to have SELECT statements defined in that way. Question is what will A5 say because this situation is nothing special (form on set with tables using WHERE caluses and Record list-Combo box on that form).

I can not try my situation with native .dbf tables because they do not have SELECT statement when we create them. Native tables do not have specifics of active link tables.

Peter, many thanks for helping me. I will rethink my form desing in accordance with your conclusion: "I try to stay away from sets as much as possible. I try to restrict all data entry to one-table forms or Xdialogs". But at this moment and for my way of using A5 it is easier and clearer to do that using set.

I still ask other colleagues to try my example and help me. If anybody from Alpha Software reads this, let me say what is the solution for my problem. Where am I wrong ?

Thanks,

Dordije.

[dordije]

Re: Active link table example with argument passing problem

Hi Peter,

meanwhile I tried to create form based on one active link table, as you suggested. It is table FIRCAR. I put a few fields from table FIRCAR on that form. Among them is also foreign key field for row in table CARTYP. I have this field in table FIRCAR. That field on this form is Type-in field.

When I open this form using

FORM.view("frmFIRCARS","","","","",mArgs)

command, Alpha Five do not prompt me for arguments.

Next, I changed type of foreign key field to Record list-Combo box, in the way I already described. After that, when I open form with same command, A5 prompts me for argument value !

Obviously, A5 needs argument value for table CARTYP which is "From table" in Choices properties tab of that field.

So this happens also on one-table form. Is there any mechanism to pass argument to Record list-Combo box control on that form ?

Thanks,

Dordije.

[Peter.Wayne]

Re: Active link table example with argument passing problem

Dordije,
This should give you some idea of ways to work with Xdialog and SQL tables. I hope you can modify this code to do what you need to do.

dim cn as sql::connection
cn.open("::name::APPTEST")  ' named connection string to database

tables=comma_to_crlf("firms, cartype, firmcars")
for each tbl in tables
	sql="DROP table if exists "+tbl.value
	if .not. cn.execute(sql) then
	    ui_msg_box("ERROR DROPping table",cn.CallResult.text)
	    end 
	end if
next

'cartype	
sql=<<%txt%
CREATE TABLE if not exists `cartype` (
  `typeID` int(8) default NULL,
  `firmID` int(8) default NULL,
  `typnam` char(20) default NULL
) 
%txt%
if .not. cn.execute(sql) then
    ui_msg_box("Error in create table",cn.CallResult.text)
end if

'firmcars	
sql=<<%txt%
CREATE TABLE if not exists `firmcars` (
  `nalrec` int(11) NOT NULL,
  `firmID` int(8) default NULL,
  `typeID` int(8) default NULL,
  `dateBuy` date default NULL
) 
%txt%
if .not. cn.execute(sql) then
    ui_msg_box("Error in create table",cn.CallResult.text)
end if

'firms	
sql=<<%txt%
CREATE TABLE if not exists `firms` (
  `firmID` int(8) default NULL,
  `firmName` char(40) default NULL
) 
%txt%

if .not. cn.execute(sql) then
    ui_msg_box("Error in create table",cn.CallResult.text)
end if

sql="INSERT INTO firms (firmID, firmName) VALUES (:firmID,:firmName)"
dim args as sql::arguments
args.clear()

firms=<<%list%
1,Peter's Classic Cars
2,Dordije's Car Rental
3,Alpha Auto Repair,
4,Vista Vehicles
%list%
for each firm in firms
	args.set("firmID",word(firm.value,1,","))
	args.set("firmName",word(firm.value,2,","))
	if .not. cn.execute(sql,args) then
    ui_msg_box("Error INSERTing",cn.CallResult.text)
    end
	end if
next 

sql="INSERT INTO carType (typeID, firmID, typnam) VALUES (:typeID, :firmID, :typnam)"

cars=<<%list%
1, 1, Chevrolet Volt
2, 1, Toyota Land Cruiser
3, 1, Nissan Sentra
4, 2, Ford Fiesta
2, 2, Toyota Land Cruiser
5, 3, Volkswagen Golf
6, 3, Fiat 850
7, 3, Mini Cooper
7, 1, Mini Cooper
%list%

args.clear()

for each car in cars
	args.set("typeID",word(car.value,1,","))
	args.set("firmID",word(car.value,2,","))
	args.set("typnam",word(car.value,3,","))
	if .not. cn.execute(sql,args) then
	    ui_msg_box("Error INSERTing",cn.CallResult.text)
	    end
	end if
next

firmCars=<<%list%
1,1,2,20090715
2,1,1,20090610
3,1,3,20081212
4,2,2,20081111
5,2,4,20090501
%list%

args.clear()
sql="INSERT INTO firmcars (nalrec, firmID, typeID, dateBuy) VALUES (:nalrec,:firmID,:typeID,:dateBuy)"
for each firm in firmCars
	args.set("nalrec",word(firm.value,1,","))
	args.set("firmID",word(firm.value,2,","))
	args.set("typeID",word(firm.value,3,","))
	args.set("dateBuy",stod(word(firm.value,4,",")))
	if .not. cn.execute(sql,args) then
	    ui_msg_box("Error in INSERTing",cn.CallResult.text)
	    end 
	end if
next 

' now, let's inspect the tables

sql="SELECT concat('{DATA=',firmID,'}',firmName) FROM firms ORDER by firmName"
if .not. cn.execute(sql) then
    ui_msg_box("Error executing SELECT",cn.CallResult.text)
    end
end if

dim rs as sql::resultSet
rs=cn.ResultSet 

firmList=rs.ToString()


ui_dlg_box("Select Firm",<<%dlg%
Double-click to select a firm;
[%O={@@}%.30,10oneFirm^#firmList!sel_*];
<Close>;
%dlg%,<<%code%
if a_dlg_button="sel_dblclick" then
	a_dlg_button=""
    chooseCar(local_variables())
end if
if left(a_dlg_button,3)="sel" then
	a_dlg_button=""
end if
%code%)
cn.close()
end 

function chooseCar as v(vars as p)
Dim format as p 
Format.tab_stops="1,2"
Format.odd_row_color="White"
Format.even_row_color="Blue White"
Format.odd_selected_color="Dark Blue"
Format.even_selected_color="Dark Blue"
Format.font="Arial,10"
Format.font_color_unselected="Black"
Format.font_color_selected="White"
Format.lastbutton="OK"
Format.group_size=1
Format.number_rows=.f.
Format.alternating_bands=.t.
	
with vars	

sql="SELECT firmName from firms WHERE firmID="+oneFirm
cn.execute(sql)
rs=cn.ResultSet 
firmName=rs.data("firmName")

sql=<<%txt%
SELECT concat('{DATA=',nalrec,'}',firmCars.dateBuy,'|', typnam) as onerec FROM firmCars
INNER JOIN cartype on cartype.typeID=firmCars.typeID
WHERE firmCars.firmID=%txt%+oneFirm

choices=cn.ToString(sql)
formattedChoices=a5_owner_draw_list_fmt(choices,format)

dlgTitle="Inventory for "+firmName
ui_dlg_box(dlgTitle,<<%dlg%
[%d;O={@@}%.40,10oneCar^#formattedChoices!chosen];
<Close>;
%dlg%,<<%code%
if a_dlg_button="chosen" then
	a_dlg_button=""
    ui_msg_box("You chose ","nalrec is "+oneCar);
end if
%code%)	
end with	
end function

[dordije]

Re: Active link table example with argument passing problem

Hi Peter,

I was on some vacation in past days, so I saw your message yesterday.

Thanks for your code. I am very grateful for time you spent in making code example for me. I need some time to examine your code and try to adapt it on my example.

I did not work with xDialog. I try to use A5 form designer as much as I can, and to base my application on A5 designed forms and on active link tables. One key reason I bought A5 was: to easy design forms and reports and to code events. Thats why I do not wish to dig in xDialog as newbie. I am not old user of A5, and I do not want to use A5 earlier techiques for user interface design. I think that these new features (active link tables) are still young and I must wait a few updates until all malfunctions will be resolved.

Thanks again,

Dordije.

[dordije]

Re: Active link table example with argument passing problem

Hi Peter again !

I used part of your code, one with ui_dlg_box() function. This is because I allready have Firebird database and all data in it.

First I prepared list of choices using SELECT statament with arguments. I got dialog box with list of car type names for particular firm. When I choose one type name, ui_msg_box appears displaying what I selected.

This approach means that I must stay away from A5 form designer and use xDialog. Well, this takes too much time in programming user interface part of application.

To be honest I hoped not to code user interface. I hoped to design it using some visual tools and to code things I must, like are business rules, events etc. And all that using SQL databases.

Thanks,

Dordije.