Table Widget with Join Table. Editable switch names not work

[crisp pier]

Hi at all.
Iam need you help, for i think so easy solution but iam to blind or stupid, to find it out.
I have 3 mysql tables
kunden : coloums: id firma, vorname,nachname)
software : cloumouns : id,name
lizenzen : coloums : ID,lizenzschlüssel,kunde (join to id kunden),software (join to id software)

This is the widget query for lizenzen

<code>
SELECT
lizenzen.ID,
lizenzen.lizenzschlüssel,
kunden.ID AS kundeID,
CONCAT(kunden.firma,' | ',kunden.vorname, ' | ', kunden.nachname) AS kunden,
software.ID AS softwareID,
software.name AS software
FROM
lizenzen
JOIN
kunden ON lizenzen.kunde = kunden.ID
JOIN
software ON lizenzen.software = software.ID;
</code>

Now i would have drop down menus for kunde and software if i change them.
So i added field typ select and use an select query.
this will work fine.
And now comes my problem.
if i edit the field and select one ofb them then i get the id back.
So in the field the id is shown not the text.
If i will save it, i get an error because in the the other field i not edited, says me is not an integer. Jep its text.
How can i finish it to show text but at save the ids will used to save.
I dont know how the safe query must look.
And my goal is to make an multiedit rows.
but i stopped at single row edit now, so that multi row edit is far in future
I make an video to demonstrate what i mean.
Please oben the video in VLC under Linux in Celluloid it will not open.
Video is created with simplescreenrecorder

Thank you very much!
Best regards Stefan

Table Widget with Join Table. Editable switch names not work

[cobalt depot]

Hello, we are closing this ticket as we haven't heard back from you in a while. If your issue has not yet been resolved, you can reply to this thread to have it re-opened.

[crisp pier]

Hi is not solved.
I hope you can help me.
Thank you very much!
Best regards Stefan

[cobalt depot]

Hello Stefan, I'm AppyBot, your Appsmith support bot. I'm here to help you with your query. Could you please provide more details about the issue you're facing or the assistance you need with Appsmith?

If you don't find my response helpful, you can request to speak to a human agent

[static heath]

Sure, I can help with that. It sounds like you need a way to display text in your dropdown menus while storing the corresponding IDs in the database. To achieve this, you can use Appsmith's select widget with custom value binding for both displaying and saving the correct data.

Here’s how you can set it up:

Step-by-Step Guide:

1. Create Select Widgets for kunde and software:

   • Add two select widgets to your form, one for kunde and one for software.

2. Configure the kunde Select Widget:

   • Set the Option Labels to display the concatenated text of firma, vorname, and nachname.

   • Set the Option Values to the id of kunde.

   • Use the following query to populate the options:

     SELECT id, CONCAT(firma, ' | ', vorname, ' | ', nachname) AS kunden
     FROM kunden;
     

3. Configure the software Select Widget:

   • Set the Option Labels to display the name of the software.

   • Set the Option Values to the id of the software.

   • Use the following query to populate the options:

     SELECT id, name AS software
     FROM software;
     

4. Bind the Selected Values to the Form:

   • In your form where you want to edit the lizenzen, bind the select widgets to the kunde and software fields. Make sure to use the Option Values (IDs) for the save operation.

   • For example, set the defaultSelected value of the kunde select widget to:

     {{ lizenzenTable.selectedRow.kundeID }}
     

   • Similarly, set the defaultSelected value of the software select widget to:

     {{ lizenzenTable.selectedRow.softwareID }}
     

5. Create Save Query:

   • Write a query to update the lizenzen table with the selected values from the dropdowns. For example:

     UPDATE lizenzen
     SET lizenzschlüssel = '{{ lizenzenForm.data.lizenzschlüssel }}',
         kunde = '{{ lizenzenForm.data.kunde }}',
         software = '{{ lizenzenForm.data.software }}'
     WHERE ID = '{{ lizenzenForm.data.ID }}';
     

6. Handle Multi-Edit Rows:

   • For multi-edit functionality, you can create a form that iterates over the selected rows and applies the update query to each row.

Here’s a brief example of what the final save query might look like in Appsmith:

UPDATE lizenzen
SET lizenzschlüssel = '{{ lizenzenForm.data.lizenzschlüssel }}',
    kunde = '{{ kundeSelect.selectedOptionValue }}',
    software = '{{ softwareSelect.selectedOptionValue }}'
WHERE ID = '{{ lizenzenForm.data.ID }}';

Docs:

Select widget label and value

By following these steps, you should be able to display the text in your dropdowns while saving the corresponding IDs in the database. Let me know if you need further assistance!

[crisp pier]

Hi, Thank you!
So i must create the select widgets outside from my table? Because i wish i can use the select widgets inside my table, so i click on the pen. The select drop down shows, i select it and click on save.
But i cant save, because the other fields have text and not the id.
I think i make it from base on wrong.
Here are the screenshots to explain what i wish to have.

Thank you very much!
Best regards Stefan

[cobalt depot]

Hello Stefan! This is a known bug: https://github.com/appsmithorg/appsmith/issues/26188. Please add a comment with your use case to help us prioritize it.
Thank you for your continuous support and patience. You can track the GitHub issue that addresses the matter further. Our development team will be taking over from here.

We value your feedback and would appreciate hearing about your experience with our support team.
https://survey.frontapp.com/v2/09a400bf433bc9676d67/3adb890ff6e0b84acde5812a67249793

[crisp pier]

It seems it is fixed. Or not? I pulled the latest appsmith version and it seems the bug is still there.
What must i do, to apply the fix?
Thank you very much!
Best regards Stefan

[cobalt depot]

It is not yet fixed. The GitHub issue is still open. The PR that was merged was only a partial fix and, therefore, it got reverted. Our team is currently working on a holistic fix on priority. Please track the GitHub issue to get updates on the status.