How to make ENUM variable to display as selection field?

(3 posts) (2 voices)
  1. pac, Member

    We are trying to fix appointment for each entry in Report 1 by using Report 2 as form page. But the Enum variable in Report 2 still displays as text field. How to fix it?

    Report 1

    CREATE PROCEDURE `sp_DBR_sales_roadshow_list`(inLogin varchar(128) )
    Select 'dbr.title','Sales List & Meeting Update 1';
    select 'dbr.hidecolumn','aid','cid','TID','s_meeting_id';
    select 'dbr.export.options','disable','csv,excel,pdf,sql';
    select 'dbr.url', '','Open CRM','new_window','record=aid','[Company]';
    select 'dbr.url', '','Open CRM','new_window','record=cid','[Name]';
    select '','sp_DBR_sales_Roadshow_nameedit','[vName]','popup', 'v_cid=[cid]', 'v_Name<=[vName]';
    -- The above report (link) doesn't push the value to DB
    select '','sp_DBR_sales_roadshow_appointment','[Appointment]','popup', 'vcid=cid','vtmid=TID','meetingid=s_meeting_id','vcstatus<=Status','vtstatus=Status','vstart<=[sdate]';
    -- The above report (link) passes value to the popup report, but the ENUM field in the popup is not rendering as a pick list. SELECT
    'dbr.rownum' as 'S.No.',
    a.`TM Name` IS NULL, 'Assign', a.`TM Name`
    ) as 'TM Name[vName]',
    a.`TM Id` as 'TID',
    if(T_Date IS NULL, 'New', 'Edit') 'Appointment',
    date(T_Date) as 'Date[sdate]',
    a.s_meeting_status as 'Status[Status]',
    a.`Company Name` 'Company',
    a.`cid` 'cid[cid]',
    a.`C Street` 'Contact Street',
    a.`C City` 'Contact City',
    a.`C State` 'Contact State',
    IF(b.Team IS NULL, '', b.Team) 'Reporting',
    a.`Reports To`,
    a.`Email Address`,
    a.`Work Phone`,
    a.`Street` 'Company Street',
    a.`City` 'Company City',
    a.`State` 'Company State',
    db2.sales_list a
    left join (
    `Reports To`,
    count(cid) 'Team'
    `Reports To` IS NOT NULL
    group by
    `Reports To`
    ) as b on b.`Reports To` = a.`Name`
    MATCH(a.Title, a.department) AGAINST (
    'manager director sales technical application -project -account -consult -consultant -exec -executive -specialist -architect -program -engineer -planning -marketing -channel -representative -representitive' IN BOOLEAN MODE
    AND (
    a.`C State` IS NOT NULL
    AND a.`State` IS NOT NULL

    Report 2 (Here the vcstatus is not rendering as pick/selection field)

    CREATE PROCEDURE `sp_DBR_sales_roadshow_appointment`(
    vtmid varchar(36),
    vcid varchar(36),
    meetingid varchar(36),
    vcstatus ENUM('Planned','Held','Not Held'),
    vtstatus varchar(25),
    vstart date
    Declare vuid varchar(36) Default uuid(); if length(meetingid)>1 then
    update db1.meetings set date_modified=now(),date_start=vstart,status=vcstatus where id=meetingid;
    update db1.meetings_contacts set date_modified=now(),accept_status=vcstatus where meeting_id=meetingid;
    update db1.meetings_users set date_modified=now(),accept_status=vcstatus where meeting_id=meetingid;
    update db2.sales_list set T_Date=vstart,sugar_meeting_status=vcstatus where cid=vcid;
    insert into db1.meetings (
    id, name, date_entered, date_modified,
    modified_user_id, created_by, description,
    deleted, assigned_user_id, location,
    duration_hours, duration_minutes,
    date_start, parent_type, status,
    vuid, 'Sales Road Show', now(), now(),
    vtmid, vtmid, 'Sales Road Show',
    '0', vtmid, '', '0', '0', vstart, 'Contacts',
    vcstatus, vcid
    ); insert into db1.meetings_contacts (id,meeting_id,contact_id,accept_status,date_modified,deleted) values(uuid(),vuid,vcid,vcstatus,now(),0);
    insert into db1.meetings_users (id,meeting_id,user_id,required,accept_status,date_modified,deleted) values(uuid(),vuid,vtmid,'1',vcstatus,now(),0);
    update db2.sales_list set sugar_meeting_id=vuid,T_Date=vstart,sugar_meeting_status=vcstatus where cid=vcid;
    end if; select 'mydbr.refresh'; END

  2. myDBR Team, Key Master

    Word of an advise, do not use ENUM at all. It's *much* better to use a proper parameter table / dynamic query. ENUM is a MySQL specific (+PostgreSQL) feature, that you really should avoid.

    The meetings.status, meetings_contacts.accept_status and meetings_users.accept_status in SugarCRM are varchar's, not ENUMs.

    To show the status as select list parameter, you can make a parameter query for it and make the parameter query a select list:

    select 'Planned',
    select 'Held'
    select 'Not Held'

    myDBR Team

  3. pac, Member

    Yes in CRM they are varchars, but mydbr users should select from list than entering it. Anyway the parameter solution worked.
    Thank you.


You must log in to post.