x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 4 replies
  • Subscribers 74 subscribers
  • Views 536 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Michele Gaw

Creating a Case within a Case from a Custom Entity

Posted By

As my subject indicates, I am attempting to create a case from within a case from a Custom Entity (using workflow) and I am getting the following error and I am not sure how to resolve it. The error appears multiple times on the screen (probably more than 10 times).

tabsql: SELECT Case_CaseID FROM vCases WHERE Case_CaseID = 123 AND (prop_status not in ('Opportunity','PendingCRE','PendingSync'))
SQL Error

I think the issue might be vCases in the error message above...but I am not sure where to address/change that. This error appears on the page when I click the Global Rule -- Create Tenant Repair. The Global rule calls a custom file --> cases/InspTenantRepair.asp There are two blocks on the asp page and they both pull their data from the cases table. I updated the associated view on the blocks to vCasesPropertyData.

Despite the error on the screen, the blocks appear below the error message. I can enter the work order and save it. I am not landing where I would like after saving..but I think I can work that out.

For now, I need to figure out how to resolve the error message appearing on the screen. The log shows the following error

errormsg 0 SELECT Case_CaseID FROM vCases WITH (NOLOCK) WHERE Case_CaseID = 123 AND (prop_status not in ('Opportunity','PendingCRE','PendingSync')) Invalid column name 'prop_status'

prop_status is not included in vCases. This is a core view that has not been modified.

(prop_status not in ('Opportunity','PendingCRE','PendingSync') This is included on the Work Order tab in the SQL area on the custom entity Property.

Any assistance on resolving the error message on the screen would be greatly appreciated!!!

Here is some other background on what I am trying to do. This client currently on 7.3.c.1 (They need to upgrade servers before we can upgrade them.)

We are working with a client that has multiple Case workflows. The cases operate as Work Orders. These Work Orders are related to a custom entity called Property.

The different Work Orders relate to inspections and repairs. They have three types of inspections and two types of repairs. Therefore, they have a total of five different work order (cases) workflow.

This is all setup and working as expected. However, the client wants to be able to create a repair work order from an inspection Work Order. In order to accommodate this, we have added two Global rules to each of the three different types of inspections. The different types of repairs are Tenant or Landlord and each have their own workflow.

Typically you can create a case from a workflow rule. However, I don't know how to pass the correct workflow if I use the Action Create Case.

Each of these workflows has their own asp page in order to assign the correct workflow.

Again...Any assistance would be greatly appreciated!!!

  • 0

    TabSQL error? That would indicate that you have an SQL clause in a Tab menu that is referencing data that in not appropriate.

  • 0

    Thanks Jeff. I do agree that the error is related to the tabs on the custom entity of Property. I am just not sure what I am doing to cause the errors to appear. As indicated the error shows up more than 10 times on the screen. The cases tab (called Work Orders) should only appear on the Property if prop_status not in ('Opportunity','PendingCRE','PendingSync'). That is what is in the SQL clause of the cases tab on the property. All works fine until I try to create a case while in a case. Not sure if URLs help explain it better but this is my "path" and I must be doing something wrong along the way.

    1. Here I am on the Cases tab for Property. Where it displays a list.

    crm/CustomPages/Cases/PropertyWOList.asp?SID=22979032828367&Key-1=58&Key0=58&Key1=6&Key37=54&Key58=54&

    J=Cases/PropertyWOList.asp&F=property/propertysummary.asp&prop_PropertyID=54&T=Property&Capt=Work+Orders

    2. I click a button on that list to create a new inspection

    crm/CustomPages/Cases/NewWOPropertyInspection.asp?SID=22979032828367&Key-1=58&Key0=58&Key1=6&Key8=191&Key37=54&Key50=22658&Key58=54&F=Cases%2FPropertyWOList.asp&J=Cases%2FNewWOPropertyInspection.asp

    3. After I save the inspection WO...

    crm/eware.dll/Do?SID=22979032828367&Act=281&Mode=1&CLk=T&Key-1=58&Key0=58&Key1=6&Key8=191&Key37=54&Key50=22658&Key58=54&key8=192

    4. While on this Case (an inspection) the client wants a workflow step to create a "repairs" case. Therefore, there is a Global rule on the inspection workflow. When I click on that Global rule. The error appears. This is what the URL looks like on the error page.

    crm/CustomPages/cases/InspTenantRepair.asp?SID=22979032828367&F=&J=cases/InspTenantRepair.asp&Key-1=58&Key0=8&Key8=191&Key37=54&Key50=22658&Key58=54&trid=20921&I=&HNA=281&E=Cases

    I do understand that the error is occurring because vCases is being used and prop_status is not in that view. Since vCases is a core view, is there anyway for me to resolve the error on the screen without modifying the core view. I don't have a way to modify the view that the tab is referring to do I. Or is that controlled by the view that the list associated with the tab is using...hhmmm. Perhaps I need to try changing that to my vCasesPropertyData view.

    I will give that a try and see if that changes anything. But if you are reading this and saying...that won't help you silly girl...please shove my in the right direction if you are seeing the error of my ways.

    Thanks!

  • 0

    No luck there. The list is using a view called vCasesProperty not vCases which is the cases view where the property id is not null.

    Any assistance you can provide would be greatly appreciated! Or if there is a better way to create a case within a case, please push me in the right direction. Thank you!!

  • 0

    OK...so I think I might have resolved the error messages on the screen. I had mentioned that the error message appeared more than 10 times. Well, I am guessing it appeared 18 times to be exact. The error message occurred for EVERY tab that I had an SQL clause for the property entity. The SQL clause was restricting the display of the tab based on the property status or stage. These are tabs that have nothing to do with the Cases tab so it seems odd that they would case an issue where they did. However, I am creating the case within the context of the property...but it still seems odd to me.

    I removed all the SQL clauses on the property tabs. They were as follows (18 of them)...Tab Name, SQL Clause. Each one of these had resulted in an error. After removing all these, my error was gone.

    Fund List prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Tenant prop_status in ('Opportunity','PendingCRE','PendingSync')

    Lease prop_status in ('Opportunity','PendingCRE','PendingSync')

    Insurance prop_status in ('Opportunity','PendingCRE','PendingSync')

    Taxes prop_status in ('Opportunity','PendingCRE','PendingSync')

    Work Orders prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Maintenance prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Security prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Roof prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Tenants prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Leases prop_status not in ('Opportunity','PendingCRE','PendingSync')

    InsurancePM prop_status not in ('Opportunity','PendingCRE','PendingSync')

    TaxesPM prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Preferred Vendors prop_status not in ('Opportunity','PendingCRE','PendingSync')

    All History prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Management Fees prop_status not in ('Opportunity','PendingCRE','PendingSync')

    Due Diligence Checklist prop_cs_stage in ('DueDiligence', 'ReadyToReview', 'Closing')

    Closing Checklist prop_cs_stage = 'Closing'

    I still need to address the display of these tabs at the appropriate time. Therefore, I am hoping that I can control these tabs in custom content by using your article .... community.sagecrm.com/.../sage-crm-7-2-controlling-tabs-with-the-client-side-api.aspx

    Thank you for your assistance!

*Community Hub is the new name for Sage City