Double QDataWidgetMapper

Hello to all,
I've a simple situation that bring me to ask your help for a stupid (I hope) problem.
I tried to look for the internet but... no way :crying:

This is the case: I'm trying to create a program to fill an administrative document (i.e. an envoice).
I have an header and some details into two different tables. My problem is on the header; it contain also the CodForn field that is the foreign key of a Fornitori (suppliers) table.

OK I've create my QDataWidgetMapper and create a QSqlRealtionalTable model to map all my widget to the header table.
Now, the problem is that I need to map all Fornitori fields so that if my supplier exists the user find it into the table, if it not exist, the user can insert the data by that 3 fields. To got this, i create an other QDataWidgetMapper to map Fornitori table, and using the ManualSubmit I save the data of fornitori before save the header data.

I thought I could be simple but... the submit() return true but fornitori info not created :(

I attach my code, I'm testing it by update an existing element (so using currentIndex of mapper to select data of header table) changing the supplier information. Have you any ideas about where is my mistake??

Thanks a lot for your time.

Michelel

Attachment 10643
Attachment 10644
Attachment 10645

I ddn't really understand your problem but I think having 5 data widget mappers does not seem to be a good approach. For complex data situations I usually opt for creating a completely custom model that contains all the data I need and deals with loading and saving of the data according to the data flow model defined for the application.

Consider two model.
1) QSqlRelationalTableModel and QDataWidgetMapper to map data of envoice header.
2) QSqlTableModel to map supplier data.

I would got this: as you see in .ui file, I've 3 field for supplier's data. When I add a new envoice header, I would like that, compiling those fields, i add into Fornitori tables all data of new supplier and the code created (AUTOINCREMENT ID on table Fornitori) should be used to fill the CodForn (foreign key) field of the envoice header table.

The same sould be possibile also for the editing; If I edit a header data, I could need to change the supplier data in two way: or changing address or changing completely supplier, so I'd like that, the changes, by submit() of the mapper linked to Fornitori table, are saved into Fornitori table and update the foreign key into header table.

I attach, a database schema of that two tables.

Attachment 10651

How could I got it?

I hope that I explain my need more clearly ;)

Thanks a lot for your help

Michele

This is my code:

Code:

---

TstRdA::TstRdA(QString id, int CodUff, QWidget *parent) :
    QDialog(parent),
    ui(new Ui::TstRdA)
{
    ui->setupUi(this);
 
    idTest=id;
    codUff=CodUff;
 
    mdlTestRdA = new QSqlRelationalTableModel(this);
    mdlTestRdA->setTable("TestataRdA");
    mdlTestRdA->setEditStrategy(QSqlTableModel::OnManualSubmit);
    mdlTestRdA->setFilter("CodUfficio=CodUffPC  and CodUfficio=CodUffEN and CodUfficio=CodUffVS");
 
    connect(ui->cboEnte,SIGNAL(currentIndexChanged(QString)),SLOT(currentEnteChanged(QString)));
    connect(ui->cboConto,SIGNAL(currentIndexChanged(QString)),SLOT(currentContoChanged(QString)));
    connect(ui->cboSpesa,SIGNAL(currentIndexChanged(QString)),SLOT(currentSpesaChanged(QString)));
    connect(ui->txtFRagSoc,SIGNAL(editingFinished()),this,SLOT(getInd()));
    connect(ui->cmdSalva,SIGNAL(clicked()),this,SLOT(Salva()));
 
    mdlForn = new QSqlTableModel(this);
    mdlForn->setEditStrategy(QSqlTableModel::OnManualSubmit);
    mdlForn->setTable("Fornitori");
 
    mapForn= new QDataWidgetMapper(this);
    mapForn->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    mapForn->setItemDelegate(new QSqlRelationalDelegate(mapForn));
    mapForn->setModel(mdlForn);
    mapForn->addMapping(ui->txtFRagSoc,mdlForn->fieldIndex("RagSoc"));
    mapForn->addMapping(ui->txtFInd,mdlForn->fieldIndex("Indirizzo"));
    mapForn->addMapping(ui->txtFCAP,mdlForn->fieldIndex("CapLocProv"));
 
    int custIndexF=mdlTestRdA->fieldIndex("CodForn");
    mdlTestRdA->setRelation(custIndexF,QSqlRelation("Fornitori","IDForn","IDForn"));
 
    mdlForn->select();
 
    cforn = new QCompleter(mdlForn,this);
    cforn->setCompletionColumn(1);
    ui->txtFRagSoc->setCompleter(cforn);
 
    int custIndexE=mdlTestRdA->fieldIndex("CodEnte");
    mdlTestRdA->setRelation(custIndexE,QSqlRelation("Enti","IDEnte","DescrEnte"));
 
    int custIndexVS=mdlTestRdA->fieldIndex("CodVoceSpesa");
    mdlTestRdA->setRelation(custIndexVS,QSqlRelation("VociSpesa","IDSpesa","DescrSpesa"));
 
    int custIndexCC=mdlTestRdA->fieldIndex("CodCCosto");
    mdlTestRdA->setRelation(custIndexCC,QSqlRelation("PianoConti","IDConto","DescrPConti"));
 
 
 
 
    mdlEnti = mdlTestRdA->relationModel(custIndexE);
    mdlEnti->setFilter(QString("CodUffEN=%1").arg(codUff));
    mdlEnti->setEditStrategy(QSqlTableModel::OnManualSubmit);
    ui->cboEnte->setModel(mdlEnti);
    ui->cboEnte->setModelColumn(mdlEnti->fieldIndex("DescrEnte"));
    ui->cboEnte->setItemDelegate(new QSqlRelationalDelegate(this));
 
    mdlCConto = mdlTestRdA->relationModel(custIndexCC);
    mdlCConto->setFilter(QString("CodUffPC=%1").arg(codUff));
    mdlCConto->setEditStrategy(QSqlTableModel::OnManualSubmit);
    ui->cboConto->setModel(mdlCConto);
    ui->cboConto->setModelColumn(mdlCConto->fieldIndex("DescrPConti"));
    ui->cboConto->setItemDelegate(new QSqlRelationalDelegate(this));
 
    mdlSpesa = mdlTestRdA->relationModel(custIndexVS);
    mdlSpesa->setFilter(QString("CodUffVS=%1").arg(codUff));
    mdlSpesa->setEditStrategy(QSqlTableModel::OnManualSubmit);
    ui->cboSpesa->setModel(mdlSpesa);
    ui->cboSpesa->setModelColumn(mdlSpesa->fieldIndex("DescrSpesa"));
    ui->cboSpesa->setItemDelegate(new QSqlRelationalDelegate(this));
 
    mapper = new QDataWidgetMapper(this);
    mapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit);
    mapper->setModel(mdlTestRdA);
    mapper->setItemDelegate(new QSqlRelationalDelegate(this));
    mapper->addMapping(ui->txtNRda, mdlTestRdA->fieldIndex("NRdA"));
    mapper->addMapping(ui->dtDataRda, mdlTestRdA->fieldIndex("Data"),"date");
    mapper->addMapping(ui->chkInv,mdlTestRdA->fieldIndex("chkInvest"));
    mapper->addMapping(ui->chkNComm,mdlTestRdA->fieldIndex("chkNComm"));
    mapper->addMapping(ui->txtCausInv, mdlTestRdA->fieldIndex("TestoInvest"));
    mapper->addMapping(ui->txtNumComm,mdlTestRdA->fieldIndex("NumComm"));
    mapper->addMapping(ui->dtDataConsegna, mdlTestRdA->fieldIndex("DataCons"),"date");
    mapper->addMapping(ui->dtDataContratto,mdlTestRdA->fieldIndex("DataContratto"),"date");
    mapper->addMapping(ui->dtDataPrev,mdlTestRdA->fieldIndex("DataPrev"),"date");
    mapper->addMapping(ui->dtDataOScr, mdlTestRdA->fieldIndex("DataOfferta"),"date");
    mapper->addMapping(ui->dtDataOVerb, mdlTestRdA->fieldIndex("DataOffVerb"),"date");
    mapper->addMapping(ui->txtLuogoCons,mdlTestRdA->fieldIndex("LuogoCons"));
    mapper->addMapping(ui->txtResa,mdlTestRdA->fieldIndex("Resa"));
    mapper->addMapping(ui->cboEnte,custIndexE);
    mapper->addMapping(ui->cboConto,custIndexCC);
    mapper->addMapping(ui->cboSpesa,custIndexVS);
    mapper->addMapping(ui->spinBox,custIndexF);
 
    mdlTestRdA->select();
 
 
 
    if (idTest.toInt()==-1){
        mdlTestRdA->insertRow(mdlTestRdA->rowCount());
        mapper->toLast();
        Pulisci();
        setWindowTitle(this->windowTitle().replace("########","Nuova"));
 
        ui->dtDataRda->setDate(QDate::currentDate());
 
            }
    else{
        Pulisci();
 
        setWindowTitle(this->windowTitle().replace("########","Modifica"));
 
        for (int i=0; i<mdlTestRdA->rowCount(); i++){
 
            QString nrda,codente;
            nrda=mdlTestRdA->record(i).field("NRda").value().toString();
 
            QSqlQuery getEnte (QString("SELECT IDEnte from Enti where DescrEnte='%1' AND CodUffEN=%2")
                               .arg(mdlTestRdA->record(i).field("DescrEnte").value().toString())
                               .arg(codUff));
 
            while(getEnte.next())
                codente=getEnte.value(0).toString();
 
            QString idRec= QString("%1%2").arg(codente).arg(nrda);
 
            if (idRec==idTest){
                mapper->setCurrentIndex(i);
                getInd();
                break;
            }
 
            for (int f=0; f<mdlForn->rowCount();f++){
 
                QString rsGen,rsForn;
                rsGen = mdlTestRdA->record(i).field("RagSoc").value().toString();
                rsForn=mdlForn->record(f).field("RagSoc").value().toString();
 
                if(rsGen==rsForn){
                    mapForn->setCurrentIndex(f);
                    break;
                }
 
            }
 
 
 
        }
 
 
    }
 
    connect(ui->cmdSalva, SIGNAL(clicked()),this,SLOT(Salva()));
 
 
 
 
}
 
TstRdA::~TstRdA()
{
    delete ui;
}
 
void TstRdA::Salva(){
 
    //In this way I update the supplier's data
    while(mdlForn->isDirty()){
        mapForn->submit();
        mdlForn->submitAll();
        mdlForn->select();
        //mdlTestRdA->relationModel(mdlTestRdA->fieldIndex("CodForn"))->select();
 
    }
 
    //So I save the envoice's header data
    mapper->submit();
    mdlTestRdA->submitAll();
 
   accept();
}
 
void TstRdA::Pulisci(){
 
 
 
}
 
void TstRdA::currentEnteChanged(QString descr){
 
    QSqlQuery getID (QString("SELECT IDEnte from Enti where DescrEnte='%1' and CodUffEN=%2")
                     .arg(descr)
                     .arg(codUff));
 
    while(getID.next())
        ui->txtNumEnte->setText(getID.value(0).toString());
 
}
 
void TstRdA::currentContoChanged(QString descr){
 
    QSqlQuery getID (QString("SELECT IDConto from PianoConti where DescrPConti='%1' and CodUffPC=%2")
                     .arg(descr)
                     .arg(codUff));
 
    while(getID.next())
        ui->txtNumConto->setText(getID.value(0).toString());
 
 
}
 
void TstRdA::currentSpesaChanged(QString descr){
 
    QSqlQuery getID (QString("SELECT IDSpesa from VociSpesa where DescrSpesa='%1' and CodUffVS=%2")
                     .arg(descr)
                     .arg(codUff));
 
    while(getID.next())
        ui->txtNumSpesa->setText(getID.value(0).toString());
 
 
}
 
void TstRdA::getInd(){
 
    QString ragsoc= ui->txtFRagSoc->text();
 
    if(mdlForn->isDirty()){
 
 
        msg.setWindowIcon(QIcon(":/HGrid/righe.png"));
        msg.setText("Le informazioni relative al fornitore non sono state salvate.");
        msg.setInformativeText("Salvarle?");
        msg.setIcon(QMessageBox::Information);
        msg.setWindowTitle("Dati fornitore non salvati");
        msg.setStandardButtons(QMessageBox::Yes | QMessageBox::No |QMessageBox::Cancel);
        msg.setDefaultButton(QMessageBox::No);
 
        int ret = msg.exec();
        switch (ret) {
        case QMessageBox::Yes:{
            mapForn->submit();
            mdlForn->submitAll();
            break;}
        case QMessageBox::No:{
            gestForn();
            break;
        }
        }
 
 
    }
    else{
        gestForn();
    }
 
}
 
void TstRdA::gestForn(){
 
    QString ragsoc=ui->txtFRagSoc->text();
 
    mdlForn->setFilter(QString("RagSoc='%1'").arg(ragsoc));
    mdlForn->select();
 
    if(mdlForn->rowCount()>0){
 
        ui->txtFInd->setText(mdlForn->record(0).field(2).value().toString());
        ui->txtFCAP->setText(mdlForn->record(0).field(3).value().toString());
 
    }
    else {
        mdlForn->insertRow(0);
        mapForn->toFirst();
 
 
        ui->txtFRagSoc->setText(ragsoc);
        ui->txtFInd->setText("");
        ui->txtFCAP->setText("");
 
 
 
    }
 
}

---

I use txtFRagSoc to get the supplier when I edit a data, and to create It when I create new header data. in this case, If I create a new header data, all data are stored but not the CodForn field (that hold the foreign key of relation).

Any ideas about it?

Thanks a lot.

Michele

Do you insert the new row to the relation table and refresh the model before submitting changes to the main table? Your data will not magically appear in the relation table if you don't manually put it there. Similarily the foreign key will not be mapped in the main table if the relational delegate does not know about it. I really think you should implement a custom model that binds the two datasets into one.