Support » Plugin: WP Data Access » Listbox on data entry page

  • Resolved rswebmaster

    (@rswebmaster)


    Hi Peter,
    I would like to show a list box in the data entry page for some data fields.
    Does this depend on the “list value” field in the Data Designer page?
    If yes, which is the syntax for the options?
    I would show the language options like “Italian” but would save the value “ITA” in the table. Is this possible?

    Regards
    R

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Peter Schulz

    (@peterschulznl)

    Hi R,

    Interesting question! There are two ways to create a listbox.

    1) Add an enum column to your table.

    You can do this in the Data Designer. Enter your listbox values in column “List values” in the following format: ‘italian’, ‘dutch’, ‘english’ (and so on). The first value will be the default. If you prefer another default value you can enter that value to column “Default value” in format ‘dutch’. I added a screenshot to see this in action.

    https://wpdataaccess.files.wordpress.com/2019/05/2019-05-08.png

    If you like to create a listbox that supports the selection of multiple values, just change “Column type” from enum to set.

    You can also create your own create table script and execute that script in the Data Explorer (use button “Import data/Execute script(s)”). Here is an example:

    CREATE TABLE person (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      name varchar(100) NOT NULL,
      gender enum('male','female') DEFAULT NULL,
      email varchar(255) DEFAULT NULL,
      language enum('italian','dutch','english') DEFAULT 'dutch',
      PRIMARY KEY (id),
      UNIQUE KEY unique_email (email)
    );

    If you want to add a listbox that supports the selection of multiple values, just change column language as follows:
    language set('italian','dutch','english') DEFAULT 'dutch',

    NOTE
    Option 1 will store the text values in your database. So ‘italian’ will be stored as ‘italian’. Not as ‘ITA’. To store your ‘italian’ as ‘ITA’ you could create a lookup table. Which brings us to option 2.

    2) Create a lookup table and a relationship between your application table and the lookup table.

    Your lookup table could look like this (this is just an example):

    CREATE TABLE language_lookup (
      language varchar(10) NOT NULL,
      text varchar(100) NOT NULL,
      PRIMARY KEY (language),
    );

    Languages can be added like this:
    INSERT INTO language_lookup (language, text) values ('ITA', 'italian');

    You could change the person table like this:

    CREATE TABLE person (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      name varchar(100) NOT NULL,
      gender enum('male','female') DEFAULT NULL,
      email varchar(255) DEFAULT NULL,
      language varchar(10) DEFAULT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY unique_email (email)
    );

    Finally create a relationship between the tables person and language_lookup:
    – Navigate to Data Project
    – Click tab “Manage Table Options”
    – Add table person
    – Edit table person
    – Add a relationship of type “lookup”
    – Select table language_lookup as your target table
    – Select column language as Source column name as well as Target column name
    – Save your relationship
    – In area “Manage columns for data entry form for table person” select text in the lookup listbox
    – Save your settings and your done

    You can also add column text to your list table in are “Manage columns for list table of table person”.

    With option 2 you can select only one value from the listbox but column language in table person will now have value ‘ITA’.

    That was quite some typing… 🙂 Please let me know if this helps.

    Best regards,
    Peter

    Thank you Peter.
    Very exhaustive answer.

    Regards
    R

    Plugin Author Peter Schulz

    (@peterschulznl)

    Hi R,

    I’m closing this topic. I was wondering: have you been able to create a listbox?

    Best regards,
    Peter

    Yes thanks your suggestion I was able to create the listbox.

    Sorry for the offtopic, where should I log suggestions/enhancement for future versions?

    Regards
    R

    Plugin Author Peter Schulz

    (@peterschulznl)

    Hi R,

    Good to hear my post was helpful! 🙂

    I think you can use the reviews page for improvement requests. But you can use the forum as well. Anyway I’ll be happy to help! 😉

    Best regards,
    Peter

    wpsd2006

    (@wpsd2006)

    Hi just quick question

    Is it possible to lookup from “view” table ?
    I have this one view that combine multiple table to easy access. But when lookup this table it confuse the form, some value are not shown and when added it select the wrong ID.

    This is the table
    wp_users ( default wp table change it to innodb )
    customer , column : user_id (FK), customer_id (PK)
    customer_contract , column : customer_id (FK), contract_id (PK), contract_info, etc

    The relation is ( Foreign Key )
    wp_users.ID = customer.user_id
    customer.customer_id = customer_contract.customer_id

    Then I create a view for this three table which will shown
    wp_users.ID, wp_users.display_name, customer.customer_id, customer_contract.contract_id

    In the customer_contract table that generated by wp data access, I set it to lookup above view by their contract_id to find the display_name. But it get mess up, some user were shown some not.

    Then when I add new contract to the customer, it save with the wrong customer_id

    • This reply was modified 5 months ago by wpsd2006.
Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.