I was trying to use the TMS Software KanBanDatabaseAdaptor but it requires the column to be presented an integer while the category field in my database is stored as a string datatype.

I found https://www.sqltutorial.org/sql-window-functions/sql-row_number/ but cant find it implemented in EDB

I was then thinking of generating the unique integer for each unique category and then performing a join to feed into the KanBanBoard

Is there an alternative solution to on the fly convert

item1 categoryA
item2 categoryA
item3 categoryB
item4 categoryD

to

item1 1
item2 1
item3 2
item4 3

Anthony


You could use a COMPUTED or GENERATED column. I would regard COMPUTED as on-the-fly but GENERATED is "real" data.

Roy Lambert

Thanks for the reply Roy

The output would be from a query filtering an existing table which I have modified the example below.

Although I assume I could insert into a memory table?

item1 categoryA true
item2 categoryA true
item3 categoryB true
item4 categoryC false
item5 categoryD true

to

item1 1
item2 1
item3 2
item5 3

Anthony


It doesn't matter if its a query or not. COMPUTED & GENERATED columns behave almost as any other column - think of them as SQL's equivalent to Delphi's Calculated type as set in the OnCalcFields event.

When creating the table simply specify wether is computed or generated and enter the necessary sql to do the work.

As an example in one of my apps I have a phone number and I have a computed column for the STD code. The SQL to extract the STD code from the company's switchboard phone number is

SUBSTR(_Switchboard,1,POS(' ' IN _Switchboard)-1)

and the column is set up as

"_cSTDCode" VARCHAR(5) COLLATE "ANSI_CI" COMPUTED ALWAYS AS SUBSTR(_Switchboard,1,POS(' ' IN _Switchboard)-1)

This means I can use a standard Delphi table filter or SQL to extract all companies who's switchboard is say '01482'

For you a guess at the sql would be

CASE
WHEN fld = 'categoryA' THEN 1
WHEN fld = 'categoryA' THEN 2
WHEN fld = 'categoryC THEN 3
WHEN fld = 'categoryD' THEN 4
ELSE 0
END

Roy Lambert

I appreciate your feedback Roy, I not sure I explained the original problem clearly.

I have an table where the category can be added/renamed by the end user and trying to display this on the TMS KanBan boards in a column for each category.

Only active activities need to be displayed and the "category" column needs a sequential index integer 0 biased without gaps so if I create a memory table and insert the results into that with a generated category column that might be a solution, i'll give it a try.

Also, your example of telephone numbers I have a similar application but do not perform lookups on the STD code although something that I might look into but with the increase in VOIP systems i'm finding that the 44 prefix to the inbound caller ID is preventing number matching so you examples have give some possible solutions to that issue also.

Thanks again

Anthony

Anthony


I'm probably still not understanding properly. I'm not sure your example is helping me

You could try


select
runsum(1) -1,
*
from table
group by __rowid

This will give you a row counter starting with 0 BUT it will not do what you seem to want in your example, however it does seem to do what I think SQL ROW_NUMBER is doing.

You might get away with

select
__rowid-1,
*
from table

or may need a different group by or order by statement

Roy Lambert

Hi Anthony,

When I saw the TMS Kanban board I liked it, but chose to implement it myself as I found the TMS implementation a bit heavy handed.

I use an "ID" (Integer) field in the Kanban table, with a look-up to the user-defined (name) values.

CREATE TABLE KanbanLookup
(ID INTEGER GENERATED ... ,
Name VARCHAR)

CREATE TABLE KanbanTable
(PRIMARYKEY INTEGER,
KanbanOrderID INTEGER,
Name VARCHAR ...
CONSTRAINT KanbanOrderID FOREIGN KEY ("KanbanOrderID") REFERENCES KanbanLookup ("ID)"
)

All that is then needed is a mechanism in the UI to map the stored ID onto the lookup Name. There are a number of ways of doing this, such as Lookup Combo Boxes.

It can even be useful to show the name using something like:

CAST(ID) + ': ' + Name

so that the "Name" entries are ordered.

Adam

Roy,

Thanks, I was able to use the runsum(1)-1 to generate a query to produce the required results.

Adam,

Interesting as after generating the correct result set I connected the TMS KanBan DatabaseAdaptor and can see the data at design time but get an access violation at runtime when I enable the DatabaseAdaptor with and edbQuery or edbTable but no issue with a FireDac connection.

Did you implement the entire KanBan UI or just the database adaptor, I suggested to TMS to add a feature to remember the item order