Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Charlie Mopps
    QueryObject Custom field namedid?
    Topic posted April 10, 2015 by Charlie MoppsGold Medal: 3,500+ Points 
    950 Views, 11 Comments
    Title:
    QueryObject Custom field namedid?
    Content:

    I'm trying to use QueryObject to get a large list of records.
    I'm able to get the custom fields, but I'm getting raw data. Fields that are of a type NamedID, etc... are not resolving themselves to their actual names.
    This wouldn't be a problem if I could query the Labels table like we do in ODBC but that table is hidden from us it appears.

    How can you use QueryObject and get it to resolve these names?

    I've tried building the individual custom objects AS named ids inside the generic object that is custom fields, but that's still not returning the names.

    Any ideas?
     

    Comment

     

    • Allan Schrum

      For Connect Web Services for SOAP passing in the NamedID that you wish to expand should cause them to expand. If they are not, then we need to investigate as to why not.

      May I ask your use case? If you are trying to get a large dump of data then QueryCSV is a much better API and more efficient. It uses ROQL to fetch the fields you want and expanding NamedID fields to get the "label" is as simple as adding ".LookupName" or ".Name" to the NamedID field. For example, a custom object MyPackage.MyObject with a menu field called Menu will expose the label by:

      SELECT ID, Menu.ID, Menu.LookupName FROM MyPackage.MyObject WHERE ...

      This process uses one query to return the results why the QueryObjects API will take longer to process. Of course, if you are intending to modify the objects, perhaps QueryObjects is what you need, but consider QueryCSV as well.

      Regards,

      -Allan

    • Charlie Mopps

      We are trying to replicated the entire database and continuously update it in preparation for the upcoming deprecation of ODBC so we can continue reporting... so yes, it's a large chunk of data indeed.

      QuerryCSV is very problematic, because it doesn't return data-types, and string values in the return are almost guaranteed to contain instances of whatever we use for a delimiter. If we use that we're going to have to custom map every field, one at a time, to it's place in our local Oracle tables. Given that this will be the ENTIRE database, that's a daunting task. Because your API doesn't work well with any middleware we've found, we have to code the XML by hand. I'm coding the captures in C#, packet capturing the XML envelopes and passing that to our middleware people so they can code the envelopes properly from there.

      Is there any documentation covering how to properly return a custom field namedID using QueryObjects?
      Further, is there a way to pre-build the XML envelope in such a way that it just, by default, returns all the names? I'd like to avoid having to code every single custom field by hand and then later add new ones when we create them in the application.

    • Charlie Mopps

      Here's the Soap Envelope I'm sending:

              <q1:CustomFields>
                <ObjectType xmlns="urn:generic.ws.rightnow.com/v1_2">
                  <Namespace xsi:nil="true" />
                  <TypeName>OpportunityCustomFields</TypeName>
                </ObjectType>
                <GenericFields name="tds_lead_src" dataType="NAMED_ID"
                xmlns="urn:generic.ws.rightnow.com/v1_2">
                  <DataValue>
                    <NamedIDValue>
                      <ID xmlns="urn:base.ws.rightnow.com/v1_2" />
                    </NamedIDValue>
                  </DataValue>
                </GenericFields>
              </q1:CustomFields>

       

       

      And then here's the Repsonse I get back:

                          <n3:GenericFields name="tds_lead_src"
                          dataType="NAMED_ID">
                            <n3:DataValue>
                              <n3:NamedIDValue>
                                <n2:ID id="1296"></n2:ID>
                              </n3:NamedIDValue>
                            </n3:DataValue>
                          </n3:GenericFields>

       

       

      I get the ID, but not the name. I'm assuming something's wrong with my request?

    • Allan Schrum

      The ObjectType is not needed here.

      The package "c" is missing in the request. Custom Fields are layered with package and then items in the package. If this is an old-style custom field then you are missing the package "c". For the new style system attributes, then you would use the package name you assigned with the system attribute.

      Fetch an object asking for CustomFields and see how the returned data is structured with GenericFields / GenericObjects and you will see this layering that I mentioned.

      Regards,

      -Allan

    • Charlie Mopps

      Where in the tree of objects should this appear?
      For example I'm working in opportunities at the moment, so I have:

      Opportunity.Customfields.GenericFields.... ?

      Are there any examples out there? This particular functionality seems to have changed several times since the API was released and I'm finding conflicting info all over the place.

    • Allan Schrum

      Version 1 of the API had all the custom fields at the same level as the object.

      Version 1.1 placed the custom field under CustomFields and the system attributes under CustomAttributes.

      Version 1.2 and later have placed all the types of custom fields under CustomFields. The essential change from version 1.1 was to rename the CustomAttributes field to be CustomFields, and all the data that used to be in v1.1. CustomFields is moved to package "c".

      The structure in v1.2 is:

      CustomFields (of type GenericObject)
        contains GenericFields with name set to the package name (e.g. "c")
          whose DataValue.ObjectValue contains a GenericObject
            with GenericFields with the name set to the custom field name (e.g. "tds_lead"src").

      Posts in the forums for version 1.2 are the ones you are interested in.

      Regards,

      -Allan

    • Charlie Mopps

      I've had it built that way, but I'm still not getting the name for the namedid.

      Here's my C# that's litterally cut and pasted from your documentation. I had to modify it for our QueryObjects request because there are no examples of that:

                      GenericField customField = new GenericField();
                      customField.name = "tds_lead_src";

                      GenericObject customFieldsc = new GenericObject();
                      customFieldsc.GenericFields = new GenericField[] { customField };
                      customFieldsc.ObjectType = new RNObjectType() { TypeName = "OpportunityCustomFieldsc" };

                      GenericField customFieldsPackage = new GenericField();
                      customFieldsPackage.name = "c";
                      customFieldsPackage.dataType = DataTypeEnum.OBJECT;
                      customFieldsPackage.dataTypeSpecified = true;
                      customFieldsPackage.DataValue = new DataValue();
                      customFieldsPackage.DataValue.Items = new[] { customFieldsc };
                      customFieldsPackage.DataValue.ItemsElementName = new[] { ItemsChoiceType.ObjectValue };


                      ObjTemplate.CustomFields = new GenericObject
                      {
                          GenericFields = new[] { customFieldsPackage },
                          ObjectType = new RNObjectType { TypeName = "ContactCustomFields" }
                      };

      Here's the XML that generates:

              <q1:CustomFields>
                <ObjectType xmlns="urn:generic.ws.rightnow.com/v1_2">
                  <Namespace xsi:nil="true" />
                  <TypeName>ContactCustomFields</TypeName>
                </ObjectType>
                <GenericFields name="c" dataType="OBJECT"
                xmlns="urn:generic.ws.rightnow.com/v1_2">
                  <DataValue>
                    <ObjectValue>
                      <ObjectType>
                        <Namespace xsi:nil="true" />
                        <TypeName>OpportunityCustomFieldsc</TypeName>
                      </ObjectType>
                      <GenericFields name="tds_lead_src">
                        <DataValue xsi:nil="true" />
                      </GenericFields>
                    </ObjectValue>
                  </DataValue>
                </GenericFields>
              </q1:CustomFields>

       

      And here's the XML for that field that gets returned. It has the correct ID in the return, but no name:

                          </n3:GenericFields>
                          <n3:GenericFields name="tds_lead_src"
                          dataType="NAMED_ID">
                            <n3:DataValue>
                              <n3:NamedIDValue>
                                <n2:ID id="1296"></n2:ID>
                              </n3:NamedIDValue>
                            </n3:DataValue>

       

       

      I actually get ALL the custom fields and their coresponding IDs, but no names.

       

    • Charlie Mopps

      ...and to be clear.... My select statement looks like:

      "SELECT opportunity from opportunity where ID = 632681 limit 1"

      Which is a random record where that field is not null, just for testing purposes.

      I see you have more complicated SQL above for QueryCSV... What we're trying to do is return objects in blocks and include the names. Do we have to make a special select to get the names as well?

    • Allan Schrum

      Thanks. Do you know the ID of the objects you are querying? Or is that simply an example for this test? The template should allow you to return the name, but there might be an issue with CustomFields.

      A "Get" request with the processing options of FetchAllNames will work.

      One alternative approach is to determine the IDs that you wish to fetch (QueryCSV) followed by a Get request with those IDs listed and FetchAllNames set. It is a two-request step to do this approach, but it works around the immediate issue you've discovered with QueryObjects.

      Regards,

      -Allan

    • Charlie Mopps

      The end goal of this is to copy the entire database. So I need to get ALL fields on ALL objects. I'm just trying to get it working with a single record. In the end this will be a 1 time iterative loop through the entire database followed by by periodic lookups for updated records.

      In this case, the Primary object I'm querying is the opportunities table. This specific item is the custom field tds_lead_src. Depending on which part of rightnow I'm in that field can be called "tds_lead_src" or "c$tds_lead_src" I've tried both in this code and they both fail. In the end, I want the entire row, with all fields, as it appears in the application.

      I've found a way to dynamically build namedID's for primary objects so it returns all that data correctly. The only thing I'm having trouble with are the custom fields. Doing a Get request is undesirable, because of the number of records we're talking about.

      If we had access to the labels table like we do with ODBC, I'd just copy that as well and do the lookups via joins. But it appears Lables has been turned into something different that I can't query normally. An alternative solution, if it's possible, would be to get a blanket copy of the Labels table so we could do our joins externally.

      Barring all that, we'll have to build this entire thing with QueryCSV, but the CSV format is really messy to parse.

      We'd prefer just to keep ODBC honestly.

    • Allan Schrum

      Then I guess I do not understand why QueryCSV would not work for you. It has access to all the fields, can return everything including labels, is more efficient and higher performance (and can return data via MTOM instead of XML). The delimiter is, by default, a comma (,) but when commas are found in the data stream we double-quote the string (as per the RFC for CSV values). If the double-quoted string has a double-quote, it is escaped with a double-quote. You can also return up to 100K rows (if using a recent release and querying the reporting database).