Relating two tables

  • Resolved pauhana

    (@pauhana)


    3 years ago

    Howdy.

    Ok, I’m very new to this plugin, so not quite sure of the best approach to this. Any pointers would be greatly appreciated.

    I have two tables created as follows:

    CREATE TABLEsvs_seed_type`
    (seed_type_id int(20) NOT NULL auto_increment
    ,seed_type_name varchar(32) NOT NULL DEFAULT ”NULL”
    ,seed_type_season_warm tinyint(1) NULL DEFAULT NULL
    ,seed_type_popup_id int(20) NULL DEFAULT 0
    ,PRIMARY KEY (seed_type_id)
    ) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;
    CREATE UNIQUE INDEX seed_type_name ON svs_seed_type (seed_type_name);`

    CREATE TABLEsvs_seed_item`
    (seed_item_id int(20) NOT NULL auto_increment
    ,seed_item_photo varchar(256) NULL DEFAULT ‘NULL’
    ,seed_item_variety varchar(128) NULL DEFAULT ”NULL”
    ,seed_item_ref varchar(128) NULL DEFAULT ‘NULL’
    ,seed_item_extras tinytext NULL DEFAULT ‘NULL’
    ,seed_item_type_id int(20) NOT NULL
    ,PRIMARY KEY (seed_item_id)
    ) ENGINE InnoDB DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;
    CREATE INDEX seed_type ON svs_seed_item (seed_item_type_id);`

    It is the type field where I have questions.

    First, in the form used to enter a new seed item I wish to have a pulldown containing the names of the types, from which the administrator can select.

    Second, in the page presenting the available seed items to the end user, I would like to be able to set the color of the type name based on whether seed_type_season_warm is 0 (so blue) or 1 (so red).

    How best to do something like this? Views? (If there’s something I should read / look at, I’d appreciate getting pointed in that direction…).

    Thanks so much!

Viewing 13 replies - 1 through 13 (of 13 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @pauhana,

    For the pulldown you might want to have a look at the lookup feature, which is explained in the following tutorial:
    https://wpdataaccess.com/docs/documentation/data-projects/overview/

    For the color listbox, you can use an enum:
    seed_type_season_warm enum('Blue', 'Red')

    Does this help?

    Thanks,
    Peter

    Thread Starter pauhana

    (@pauhana)

    Thanks Peter.

    For the first question, I think I understand.

    But for the second, I’m not sure how to use the value in the type table to set the css class for the text (whether the seed_type_season is specified as an enum or a 0/1). I’m probably not thinking straight, but can I manage to easily do this?

    Thread Starter pauhana

    (@pauhana)

    Working through the tutorial…

    • This reply was modified 3 years ago by pauhana.
    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @pauhana,

    >>> But for the second, I’m not sure how to use the value in the type table to set the css class for the text…

    Can you explain your requirements? What needs to be changed when? Is this a project or a publication? Are you using it on the back-end or on the front-end?

    Thanks,
    Peter

    Thread Starter pauhana

    (@pauhana)

    Yes. Sorry, I should have been clearer.

    This is for a project. (Or should I be thinking in terms of a publication? Not sure…).

    What I am trying to put in place is a seed inventory system. What seeds are in the system will be maintained by seed “librarians” (ideally in a role restricted front end page). But then there is a front end page accessible to all logged in members that presents the current inventory list and lets them select the seeds they would like to be given.

    My main question boils down to this: For constructing the table shown to end users, is there a way in Data Access to use the information contained in the seed_type table to build the link shown to the end user?

    The pseudo code for what I am trying to do looks something like this (where the italicized portion has info pulled from the type table):

    <a class=”seed_type_season_warm sg-popup-id-seed_type_popup_id” >seed_type_name</a>

    Hopefully that makes sense. If not, I can try to clarify further…

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @pauhana,

    Thanks for your explanation! Did you use the Data Publisher or Data Projects? Can you share a screenshot?

    Thanks,
    Peter

    Thread Starter pauhana

    (@pauhana)

    Hey Peter.

    Sorry for the delayed response, my wife and I had to go out of town for a bit. I’m finally back at it.

    Here are screen shots showing the starting point of what I’m up to:

    The project
    https://ibb.co/hVQC3S3

    The menus
    https://ibb.co/wdhMyZP

    The seed type table
    https://ibb.co/HhnmgCR

    What else should I provide? (Thanks again for the help…).

    Thread Starter pauhana

    (@pauhana)

    Hey Peter.

    I think I may be asking too much of the tool. It is providing me an excellent means of using the front and back end to maintain and present data.

    A couple of specific questions, though.

    (1) In your school administration demo (https://wpdataaccess.com/demos/demos-apps/school-administration-system/), at timestamp 5:20 you show the teacher pulldown. I have that exact situation, and at this point the field in question is just filled in with an ID, which maps to the ID key of a second table. I would like to have a pulldown with the associated names used (instead of having to fill in an ID), but don’t quite get how I go about doing that.

    (2) Do you have an example of setting a default image for a field? Just wondering if I need to provide a full url to the image, or just the name of the image in the uploads directory?

    Thanks again.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @pauhana,

    >>> I would like to have a pulldown with the associated names used (instead of having to fill in an ID), but don’t quite get how I go about doing that.

    I presume the pulldown should be added to the inventory items page and showns a list of types? In that case you need to create a project template for table sv_seed_item. When you edit the project template, press tab “Relationships” and add a lookup_listbox for table svs_seed_type. I think you need the type id column for your relationship.

    Then press tab “Data Entry” and select the type name column to be displayed in your pulldown. Finally you need to do the same on tab “List Table”. This will allow users to view the type name instead of the id and search on type name.

    >>> Do you have an example of setting a default image for a field? Just wondering if I need to provide a full url to the image, or just the name of the image in the uploads directory?

    If you want to store the image path and name in a column you should select Image URL:
    https://wpdataaccess.com/docs/documentation/data-explorer/column-settings/#wordpress-media-library-integration
    The image can be store anywhere on a public URL.

    If you want to store your images in plain text, you also need to store hyperlinks as “Plain text” for your table. See:
    https://wpdataaccess.com/docs/documentation/data-explorer/table-settings/#process-hyperlink-columns
    The default is JSON which supports additional attributes.

    Hope this helps,
    Peter

    Thread Starter pauhana

    (@pauhana)

    Excellent. Works beautifully. Thank you so much, Peter.

    Thread Starter pauhana

    (@pauhana)

    Sorry, but still an issue for the pulldown. The type ID in question is not being recorded properly in the item table when adding a new item. (See screen captures below).

    Relationships –
    https://ibb.co/37rVnH6

    List Table –
    https://ibb.co/m8jXj7v

    Data Entry –
    https://ibb.co/6cKhYLQ

    Type Info –
    https://ibb.co/vxf1cnr

    Item Info –
    https://ibb.co/GnmbXsg

    Expecting that last to show 75 for the type ID. Did I miss something?

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @pauhana,

    The relationship image shows that you selected the seed_type_name as the target column. Please change this to seed_type_id.

    Does this fix the issue?
    Peter

    Thread Starter pauhana

    (@pauhana)

    Ah yes. Silly mistake. All fixed now.

    Thank you again!

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘Relating two tables’ is closed to new replies.