#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    How to create new record from values in a multi-select field


    [IMG]Hi,

    I am new to mySQL and just trying to create a table view for a lookup form on my website. My problem is that some of the fields I draw the original data from are multi-select so have one or more values.

    I need to create a new record for each of these values so for example an organisation might have stores in multiple locations and in the multi-select field it might have London, Birmingham, Margate etc. But my users need to be able to select Margate or Birmingham or London, but at the moment the new dropdown list has all the locations. I need to create a new record for each location. Is this possible?

    So far I have the following which creates a table view and replaces the current field separators (which are |##|) in the multi-select box with commas:

    CREATE VIEW GroupLookup AS
    SELECT vtiger_account.accountname, phone, website, fax, vtiger_accountbillads.bill_street, bill_city, bill_state, bill_code,
    REPLACE(vtiger_accountscf.cf_758, '|##|', ',') as cf_758,
    REPLACE(cf_760, '|##|', ',') as cf_760,
    REPLACE(cf_756, '|##|', ',') as cf_756,
    cf_762, cf_750

    FROM vtiger_account, vtiger_accountbillads, vtiger_accountscf
    WHERE
    vtiger_accountscf.cf_750 = 'Yes'
    AND vtiger_account.accountid = vtiger_accountbillads.accountaddressid
    AND vtiger_account.accountid = vtiger_accountscf.accountid
    [/IMG]
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    Originally Posted by MarkAnthony
    some of the fields I draw the original data from are multi-select ...
    how often do you have to do this?

    Originally Posted by MarkAnthony
    I need to create a new record for each location.
    how often do you want to do this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    how often do you have to do this?

    how often do you want to do this?
    Hi,

    When new records are added to my database would it update the View> If so, it would be just the once. Im sorry, but I am new to SQL

IMN logo majestic logo threadwatch logo seochat tools logo