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) )
   
   BEGIN
   
   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', 'http://10.0.0.101/crm/index.php?action=DetailView&module=Accounts','Open CRM','new_window','record=aid','[Company]';
   
   select 'dbr.url', 'http://10.0.0.101/crm/index.php?action=DetailView&module=Contacts','Open CRM','new_window','record=cid','[Name]';
   
   select 'dbr.report','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 'dbr.report','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.aid,
   
     IF(
   
       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.`Name`,
   
     a.`C Street` 'Contact Street',
   
     a.`C City` 'Contact City',
   
     a.`C State` 'Contact State',
   
     IF(b.Team IS NULL, '', b.Team) 'Reporting',
   
     a.`Title`,
   
     a.`Department`,
   
     a.`Reports To`,
   
     a.`Email Address`,
   
     a.`Work Phone`,
   
     a.`Street` 'Company Street',
   
     a.`City` 'Company City',
   
     a.`State` 'Company State',
   
     a.s_meeting_id,
   
     a.Trained_date
   
   FROM
   
     db2.sales_list a
   
     left join (
   
       select
   
         `Reports To`,
   
         count(cid) 'Team'
   
       from
   
         db2.sales_list
   
       where
   
         `Reports To` IS NOT NULL
   
       group by
   
         `Reports To`
   
     ) as b on b.`Reports To` = a.`Name`
   
   WHERE
   
     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
   
   )
   
   BEGIN
   
   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;
   
   else
   
     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,
   
     parent_id
   
   )
   
   values
   
     (
   
       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',
   
   union
   
   select 'Held'
   
   union
   
   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.

Reply

You must log in to post.