110
Views
12
Comments
Salesforce Connector Query Aggregate Invalid Structure

When attempting to utilize the Query action within our SF Integration I am receiving an error that appears to be related to the expected ToObject() Input.

Here is the Query I am running:  

"select calendar_month( cmbls__Effective_Date__c ),  calendar_year(cmbls__Effective_Date__c ), sum( cmbls__Transaction_Amount__c ) 
    from cmbls__Payment__c 
        where cmbls__Effective_Date__c >= " + FormatDateTime(StartDate,"yyyy-MM-dd") + " and 
            cmbls__Effective_Date__c <= " + FormatDateTime(EndDate,"yyyy-MM-dd") + " and 
            X18_Digit_Opportunity_ID__c in (" + OpportunityIdWhereClause + ") and 
            Reporting_Payment_Id__c ='15'
    group by calendar_month( cmbls__Effective_Date__c ),  calendar_year(cmbls__Effective_Date__c )
    order by calendar_year( cmbls__Effective_Date__c ),  calendar_month(cmbls__Effective_Date__c ) asc"

The structure being used in the ToObject() Input contains 3 attributes:

  1. CalendarMonth (Int)
  2. CalendarYear (Int)
  3. TransactionAmount (Currency)

And here is the error:

"Expected a Record of AggregateResult instead of STCollectrMonthToMonthFeeStructure"

Rank: #426

Okay - some progress has been made here, I renamed all the Structures in OS to "AggregateResult" along with the various attributes as "Unknown_Field__<#>" based on the results of the SOQL Query I am comparing against with the correct values. This has now led to an instantiated record list with the correct number of records but every attribute now has a 0 for the saved value across all records (all incorrect).


mvp_badge
MVP
Rank: #2

Hi Nick,

Are you using the Salesforce connector from the Forge? In general, if you have questions about a specific Forge component, you should ask them in that component's subforum - the maintainers will be notified, and you're likely to get a better answer than when posting it in the general forum.

That said, can you give some more details about the errors you are getting? If possible, the entire error stack? Looking at the code, the error message itself should include more (". Base: RecordType"). What the code does is trying to map what Salesforce returns to the OutSystems record, and wants their names to match (not sure why, as long as the attributes match you'd be safe). Then it tries to map all the attributes.

I would assume (but I've never used the Salesforce connector) that Salesforce uses the names you use in the select, but you seem to use calculated attributes, so perhaps you should give those a name, assuming that's possible in a Salesforce query? 

Rank: #426

Hi Kilian,

Thanks for the suggestion regarding the subforum, was unaware of that functionality. As far as the error messages go I have been able to get past those and there are no longer any errors shown. Unfortunately there is no SOQL equivalent of an 'as' statement (eyeroll) so these fields are just coming back (at least as seen through another SOQL tool, Workbench, I use to query our Salesforce environment) as Unknown_Field__1, Unknown_Field__2, and Unknown_Field__3.


I have extremely limited experience in C# (read: next to none), is there a way for me to set a breakpoint a layer lower in the Extension code in OutSystems to see if the Extension is bringing back the field names as something other than the ones shown in my other SOQL tool.

mvp_badge
MVP
Rank: #2

There is unfortunately no way to debug Extension code. The best you can do is put some debug output inside the Extension, and check the General Log for it.

The mapping is called in SForce.cs, function MssSForce_Query, ObjectMapping.SalesForceObjectToOutSystemsRecord from ObjectMapping.cs. Inside that function there are already some logging statements that are commented out. Remove the // before the "logMessage" calls, and you should have some info (or add additional ones).

Save the source file, republish the Extension from Integration Studio, republish the SalesForce_Connector Module, and republish your module(s), and you're set :).


mvp_badge
MVP
Rank: #32

Hi Nick

I am trying to recreate your issue with SalesForce but not managing to do so yet.

I have however found the following regarding SOQL on their developer site:
"You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code. "

Perhaps try adding some aliases and renaming the structure attributes to match?


Let us know if this works?


Hanno

mvp_badge
MVP
Rank: #32

Good day Nick

In the extension, in the ObjectMapping.cs class, replace this code:

if (null == sForceField)
{
// Ignore fields not returned by the describe.
logMessage("5: can't get sForceField");
continue;
} object fieldValue = DataTypeConversion.SforceToOutSystems(sForceField.soapType, element.InnerText);
logMessage("6: fieldValue is " + fieldValue);
if (null != fieldValue)
{
structFieldInfo.SetValue(osStructObj, fieldValue);
}

with

string elType = element.GetAttribute("xsi:type");
if (string.IsNullOrEmpty(elType))
                    {
                        //logMessage("5: can't get element type attribute");
                        continue;
                    }


object fieldValue = DataTypeConversion.SforceToOutSystems((soapType)Enum.Parse(typeof(soapType), elType.Replace(":", "")), element.InnerText);
                    //logMessage("6: fieldValue is " + fieldValue);
                    if (null != fieldValue)
                    {
                        structFieldInfo.SetValue(osStructObj, fieldValue);
                   }


mvp_badge
MVP
Rank: #2

I've added some formatting:

if (null == sForceField)
{
   // Ignore fields not returned by the describe.
   //logMessage("5: can't get sForceField");
   continue;
}

object fieldValue = DataTypeConversion.SforceToOutSystems(sForceField.soapType, element.InnerText);

//logMessage("6: fieldValue is " + fieldValue);
if (null != fieldValue)
{
   structFieldInfo.SetValue(osStructObj, fieldValue);
}

with

string elType = element.GetAttribute("xsi:type");
if (string.IsNullOrEmpty(elType))
{
   //logMessage("5: can't get element type attribute");
   continue;
}

object fieldValue = DataTypeConversion.SforceToOutSystems((soapType)Enum.Parse(typeof(soapType), elType.Replace(":", "")), element.InnerText);

//logMessage("6: fieldValue is " + fieldValue);
if (null != fieldValue)
{
   structFieldInfo.SetValue(osStructObj, fieldValue);
}
Rank: #426

I replaced that code and it resulted in null values for all objects so I reverted that change then removed the commenting out on Line 67, 80, & 86 of ObjectMapping.cs to allow for better logging. Additionally I did alias all the fields as "month", "year", and, "amount". Within NameMapping.cs action SalesForceFieldNameToOutSystemsFieldName it appends "ss" to the beginning of each field name, however, which, I believe, is leading to a mismapping shown in the logs. 

mvp_badge
MVP
Rank: #32

Hi Nick

Hmmm.. Not sure then what is different.

The following line of code actually sorts out the "ss" prefix mismatch - I checked for that as well.

string osFieldName = NameMapping.SalesForceFieldNameToOutSystemsFieldName(sForceFieldName);


Perhaps try the attached version, I may have made some other changes in the code that I forgot to include above.

Hanno

Rank: #426

I don't see an attached file on your most recent message - do I need to schedule an eye appointment?

mvp_badge
MVP
Rank: #32

Sorry Nick

Seems I tried to upload an oap file and the forum only allows oml files.

After I sent this I realised I broke some of the other functions in the extension, so it is just as well that the file did not upload.

Question: are you using the latest version of the SalesForce connector from the Forge?

I'll try to implement and recreate the issues again somewhere in the next two days, as my workload allows, to see if we can't get this solved.

Hanno

Rank: #426

No worries, I really appreciate you taking a deeper look into this. Yes, I am on the latest version.