Differend ID-column and display-column in QCombobox

Mr. Kibu · wrote on 10 Mar 2016, 09:37

Hi,

I set a combo like this:

modItem=new QSqlTableModel(this);
modItem->setTable("vuVoucherPosItems");
modItem->select();

ui->cbItem->setModel(modItem);
ui->cbItem->setModelColumn(0);

But I want to display column 2 in the combobox not column 0 (but the value of column 0 should be stored in the corresponding TabelModel).
I know that there is a way using QSqlRelationTableModel and relations, but for different reasons I don't want to use it.

Is there a way to do this?

SGaist · wrote on 10 Mar 2016, 22:50

Hi,

One way could be to use a QAbstractItemDelegate to change what is shown.

Out of curiosity, can you explain why you need that special setup ?

Mr. Kibu · wrote on 11 Mar 2016, 08:27

Ok, I'll try:

I have got the field "ItemKey" in my parenttable. Here I want to store the ItemKey from a related Query (sqlite) with contains the fields ItemKey and ItemText (say OriginalItems to those Items). That's no problem.

But now I want to collect Items, that are not part of the related Query, and I don't want to store there, because they are very individual. The one possibility would be to store those values in an additional column (e.g. OtherItem). But now I have two imput-widgets (one combo and one lineedit) in my imput-form.

My idea:
Make an virtuall tabel (view) with the OtherItems (selected from the parenttable) and add them with a Union-Query to the related Query. In the fields "ItemKey" and "ItemText" of the OtherItems I have the same String (e.g. "abcde"), and in the fields "ItemKey" and "ItemText" of the OriginalItems I have the Key-Value and the corresponding Text-Value.
Now I want to display this in a combobox. This all will be no problem with an QSqlRelationalTabelModel and a relationship. But with this combobox I also want to add the new individuall items (OtherItems) and here I have got problems using QSqlRelationalTableNModel.
So, I thought it would be better to make it without relations, but here I have got the problem explained above.

conclusion:
I want to store the ItemKey from the related Query in the field "ItemKey" in my "parenttable" and display the ItemText (from the related Query) in the combobox. And I want to have the possibility to insert individuall Items in that combobox.

I hope, the explaination is not to confusing. What is the best way to do that?

Thank's a lot!!

SGaist · wrote on 11 Mar 2016, 20:52

Well… I can't say it's really clear. Could you maybe make a picture of what you currently have and what you would like to get in the end ?

Mr. Kibu · wrote on 13 Mar 2016, 16:26

I have got a table (sqlite) were I want to store item that I have buyed (=table "Purchase").
Some items (that I need in my database/programm for other calculations) are stored in a separate tabel (I will call them "StoredItems").
This table "StoredItems" have got the columns ItemKey and ItemText. My table "Purchase" contains a column called "ItemKey" were I can store this "StoredItem".

Now I want to have the possibility to store other individuall items, that are not stored in my database. The easiest way would be to add a column in "Purchase" called "OtherItem" and whrite here the items (just a string). In cases were I imput "StoredItems" in my table "Purchase" there will be a key-value in the column "ItemKey" and the column "OtherItem" will be NULL. On imput a "OtherItem" it will be reverse.
I think, that I need two imput widgets in my imput-form for this soulution (or is there an other (easy!!) way???).

So I wanted to combine the two possibilities in one column in the table "Purchase" (e.g. "Item"). If there is a "StoredItem" I will write the "ItemKey" from the related table "StoredItems" and if there is an "OtherItem" I will write the String for the other Item into this column.

For the combobox-model in the imput-form I would create a table that look like this:

ItemKey | ItemText

You see, that I would take the "OtherItems" and put it in the Key-column and in the text-column (I get the OtherItems from a query in my sqlite-database and add them to the "StoredItems" with an Union-query).

Here I want to link my imput-combobox with the column "Item" in the table "Purchcase" and display the value "ItemText" from the table above.

I know that it is a very complex construction, but I don't know how to do it better/easier. Is there a better solution, or how do I display the "ItemText" in my combobox.

Additon:
My above explained construction, will basicly operate with a QSqlRelationalTableModel (I have tried already).
But I also want to add new "OtherItems" with my combobox. On a relation with QSqlRelationTableModel I don't found a possibility to do this.

Thank you in advance for your patience!!!

SGaist · wrote on 13 Mar 2016, 22:23

Shouldn't you rather have only one concept of item and then a qualifier ? e.g. a table ItemType which will contain e.g. Store and Other and in your Item table you'd have column which will be a foreign key (potentially with a unique constraint) on the ItemType table. That way you don't have to do funky logic to retrieve the items you are interested in, just standard queries/filers.

Mr. Kibu · wrote on 14 Mar 2016, 07:09

I have already such a (working) solution. In the table "Purchase" I have the columns ItemKey, Item and ItemTyp. I map the column Item with my combobox and the data in the table "Purchase" will look like this:

ItemKey |Item |ItemTyp

If the imputed data in the combobox is a StoredItem, the ItemKey is written to the column "ItemKey" otherwise the column "ItemKey" is set NULL.
The column ItemKey in the table "Purchase" has got a foreign key constraint to the column ItemKey in the table "StoredItem".
But now I have to update the column "Item" in the table "Purchase" by myself if the data in the table "StoredItem" changes in the meanwhile.

SGaist · wrote on 14 Mar 2016, 21:04

It shouldn't belong to the Purchase table. That table should only contain items whether they come from a store or somewhere else is not something that that table should handle.