Announcement

Collapse
No announcement yet.

search multiple values field

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • search multiple values field

    hi,

    - I have a field where I store multiple values with ';' separated, like

    field seq. field content
    1 A1;A2
    2 A2
    3 A1
    4 A2;A1

    - in search and quick search I should be able to search fields that contains A2 using contain parameter, and it should bring

    1, 2, 4

    - Unfortunately this feature doesn't work or I don't know how to use it.

    Please advise.

    cheers

  • #2
    Re: search multiple values field

    HI NUREACTOR,

    THERE WAS NO PROBLEM IN MY SC V6, TAKE A LOOK AT ATTACHMENT.

    CREATE TABLE `multiple_value` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `value` VARCHAR(50) NULL,
    PRIMARY KEY (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;


    CREATE FORM (MULTIPLE RECORD)--> INSERT DATA--> AND JUST QUICK SEARH.

    SUYONO
    Attached Files

    Comment


    • #3
      Re: search multiple values field

      right, it works like that, thanks.

      but if the field is a lookup field, then it doesn't work

      check this scenario:
      if A1 = tomato and A2 = potato, in quick search will not work if you look for potato. The other scenario in full search window, if you set the field to be select or double select and then set the lookup as above, then it when you search for potato you will get no results.

      cheers

      Comment


      • #4
        Re: search multiple values field

        Of Course if you have scenario above there will no result, from your post previously you ask how to search without lookup.

        If i got situation like that i will change DB Schema with 2 options :
        1. WITH CHILD TABLE
        //create fruit's master table
        CREATE TABLE `fruit` (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `fruits` VARCHAR(50) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
        )
        COLLATE='latin1_swedish_ci'
        ENGINE=InnoDB
        AUTO_INCREMENT=4;

        //create parent table
        CREATE TABLE `buyer` (
        `id` INT(10) NOT NULL,
        `name` VARCHAR(50) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
        )
        COLLATE='latin1_swedish_ci'
        ENGINE=InnoDB;

        //create child table
        CREATE TABLE `buyer_shoping` (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `buyer_id` INT(10) NOT NULL,
        `fruit` INT(10) NULL DEFAULT NULL,
        PRIMARY KEY (`id`),
        INDEX `FK__buyer` (`buyer_id`),
        CONSTRAINT `FK__buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyer` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
        )
        COLLATE='latin1_swedish_ci'
        ENGINE=InnoDB;

        2. WITHOUT CHILD TABLE
        //other option without child table, BUT YOUR FIELD TYPE OF fruits HAS TO VARCHAR
        CREATE TABLE `buyer_without_child` (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(50) NOT NULL,
        `fruits` VARCHAR(50) NOT NULL,
        PRIMARY KEY (`id`)
        )
        COLLATE='latin1_swedish_ci'
        ENGINE=InnoDB;


        if you choose option 2 you should choose Double Select and Fill in lookup setting "SELECT fruits FROM fruit".

        See attachment please.

        suyono
        Attached Files

        Comment

        Working...
        X