If new fields have been added to an object after Excel Connector has been used then they will be missing if a "Describe Sforce Object" is run.  Logging off Excel Connector and back on seemed to force a refresh such that it would then be able to show the new fields.  However, in recent months I have found that logging off and on doesn't get around the problem.  Whilst not tested well, it seems I have to reboot my Windows machine to be able to get Excel Connector to refresh what I imagine must be an object cache somewhere.

 

Does anyone know a more efficient / effective way to reset Excel Connector's cache.

 

Thanks.

Excel Connector and RSSBus Excel Add-In can be used to dump out all the fields on an Opportunity record in my Production environment.

 

However, when trying to do the same thing for the Sandbox, many felds are not returned.  The missing fields seem to be typically look ups and formulae.

 

Salesforce's Data Loader, on the other hand, can dump all the fields from the Sandbox Opportunity record.

 

Has anyone else has the same experience.  If so, is there are way around this as I make significant use of Excel Connector.

 

Thanks.

You can pass parameters to reports such as Table reports using the pv parameters on the end of the reports URL, e.g. https://emea.salesforce.com/rrrrrrrrrrrrrrr?pv0=p1&pv1=p2 will run the report with id=rrrrrrrrrrrrrrr and set the first value parameter to p1 and the second to p2.

 

I've created a report of the Joined Report Type (new in Spring '12) and have found I can't pass parameters to it via the URL as I could above.

Is there a way for me to do this or an alternative technique?

I'm familiar with the duplicate field selected message.  It usually means that I've specified the same field twice or I have the same label for two different fields.

However, neither of the above explain my current problem.

I get the following from an sfdescribe:

Service_Number_Check__c	Name	OwnerId	Account__c	Service_Name__c	Service_Number_Check__c
1st instance					2nd instance

This data is from a fairly small custom object.  I added an new field called "Dummy_field__c" to see if this might help.  I then di another sfdescribe and got 3 instances of the offending field.  The "Service_Number_Check__c" is a formula.  I suspect that this is relevant.
 

Thanks to this discussion board I have learnt that the "describeSObject" call can tell me the literal names associated with an Objects API field name.

Thanks to a colleague I have found that "<script type="text/javascript" src="/js/functions.js"></script>" is required to get onmouseovers of table entries highlighting.

Does anyone know where are these things documented?

From an SControl using AJAX how can I discover the label name for a given field (e.g. The external label name for the API field "Contract_PO__c" might be Customer PO.  How do I discover this?

I'd like a formula that takes a supplied date and returns a date which will be exactly 3 years - 1 day into the future, i.e. last day of a 3 year agreement.  For example 1 Jan 2007 > 31 Dec 2010.  In Excel I can have date made of component day or month values of 0 or less but still get a valid date, i.e. DATE(2007+3,1,1-1) which is DATE(2010,1,0) and equals 31/12/2009.

Thanks for your help.

Salesforce does not allow us to define fields that can be used as a chronological log.  We have text that can be altered or cleared and that's it.  The following code is my effort at creating a "logging field".  I've not written an scontrol with any more than alerts and prompts before and discovered quite a few things on the way.  Additionally there are other issues that I've yet to resolve.  I hope this entry is helpful to others and am also hoping some of you can see better ways to do this.

====================================

In a nutshell what I want to do is to front end the way users can edit a Salesforce Long Text Area field such that they can only append to the text already in the field and that users initial and date of update are included when they do add to the log.

Salesforce allows us to give users field level-security of no access (not visible), read only access (visible and read-only) or update access (visible and not read-only) to a field.   Page Layouts allow us to reduce this through the Salesforce front end.  Someone with update access as defined in field level-security may have no access through the page layout if the field is not available.  They can still update the field though other means such as API and Ron Hess' SF Connector is an easy example.

The code below manages access via page layouts only.  So bear in mind that users who you want to append info to the field will have the ability to alter or clear the field through the API.  Luckily most users are content to only use Salesforce via the page layout interface.


To create an "append only" field users are given update access to the field, read-only access in their page layout and a button on the page-layout which will drive an Scontrol which will allow them to make an entry which the Scontrol appends to the field.  The Scontrol actually puts the User_Alias (in our site we use this to set the user's initials) and the date in front of the appended text entered by the user and then stuffs all this into the front of the existing field contents.  This gives a reverse chronological log, where the most recent entry is at the top.

Some experiences I learnt on the way.  Some fixed, some remaining a pain for which I have no answers:
1. IE 7.0 users must enable native XMLHTTP.  Under "Help & Training" search for "What Internet Explorer browser settings are optimum for salesforce.com?" to get the full list of things that must be set to make IE work.
2.Firefox prior to 2.0 generates a console error on textareas if you use alerts for debugging.  Because it's on the console users won't see it.  Refer notes at lines 213-215
3. I gather you load a JavaScript variable to a HTML page using document.write (line 233).  The fact that all carriage return/line fees are stripped when you do this was a bit of surprise.  So in line 232 they are all changed to HTML <BR /> tags.
4. As this code is a single field update it made more sense to do this as a small pop-up where the user would launch the scontrol to do the append, add their note and exit.  The underlying page would typically be the full record display.  I gather as salesforce.com launches the S-Control this restricts the S-Controls ability to control the window.  (If the code was launched from a web link, you can define some things like screen size.  But if you call the code from a Hypertext you can't do this).
4.1 In IE you get a pop-up size screen.  In Firefox you get either a full screen or a new tab.  I want a pop-up size in both browsers.  Salesforce doesn't allow me to define this as part of a Hypertext launch so I resize once running.    Lines 99-115 make the screen max, position towards the middle and shrink it back.  Seems a bit of a kludge but gets the job done.  Firefox alas, positions in the top left corner regardless.  In Firefox options, you must set "New pages should be opened in a new window" under Tools, Options..., Tabs.  IE 6 and earlier don't support tabs.  Presumably IE 7 has a similar option of opening in new window/new tab.
4.2 I'd really rather not have the Menu Bar and Address Bar(s) showing in the pop up.  My understanding is that you can ask for them not to show if you do the Window.Open.  But as Salesforce.com launches the Window we don't get to choose :(
4.3 When the user hits Save or Cancel the Scontrol updates or doesn't update the field as requested, but then closes the pop-up.  This works fine in Firefox and IE6.  Alas IE 7 asks the user to confirm the window close.  Again, because we didn't open the window I don't believe there's a way to avoid this.
4.4 No this bit is really interesting.  I wanted to call this S-Control from a hypertext field, so that I could define a button and position it next to the field displayed on the page-layout.  To get the address of the S-Control I defined a web-link to it so I could start testing earlier (takes time to define a button) by using the properties right click and besides I have always started this way.  So initially I had the hypertext working but was a bit disappointed because I was always getting a display with the left hand frame in it - search, recent items, etc.).  Also the pop-up could only be closed by closing the window (red-x).  I couldn't programatically close on the user hitting Save or Cancel.  Oh well, something I had to live with.  At some stage something broke and when I hit the button Salesforce said my address was invalid.  Strange, I hadn't changed it.  Instead of looking up the address again from the weblink I used SF Connector to list the S-Controls ID.  When I plugged its address into the Hypertext I got a pop-up without the left hand pane.   Even better, window.close() now worked. Yeah!

I can't fit the above and the code in to a single post to the discussion board so I'll add the code first.

====

Message Edited by mikeol on 03-31-2007 07:42 PM

I guess I must be doing something dumb here, I expect I need to some help seeing the obvious.

I have a custom object (Direct_Payment).  On the Account record I have added a custom field which is a simple lookup reference to the custom object.

I can see the custom field on the Account record when I log on to Salesforce, I can export it using Sforce Data Loader and can also extract it to a spreadsheet using the ever trusty Sforce Connector.  But when I try to query it with AJAX:
    Select Name, Direct_Payment__c, Account_No__c FROM Account Where Id = 'xxxx'
then I get the dreaded "INVALID FIELD", "No such column 'Direct_Payment__c' on entity 'Account'.  Error at Row:1 Column: 14.  Note the SQL statement is the same one which works successfully in Sforce Data Loader.

I'm using Beta 3.3 of the AJAX interface and the same salesforce logon in all cases.

Thanks.

Since discovered that the logon determines the endpoint.  I need to use 7.0 to get to lookup fields:

sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}"); not
sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_60}");

Problems solved by me!

Message Edited by mikeol on 10-08-2006 06:15 PM

1. If I write a simple Java script outside of Salesforce (no AJAX, etc.) that writes some text in an alert, prompt or confirm with double spaces between words or leading spaces before a line it prints out exactly as provided, e.g. alert("     Fred") produces "     Fred".  If I do the same inside S-Control the double spaces become single and the leading spaces disappear, i.e. I get "Fred".  There's a HTML tag <PRE> </PRE> that sets whether spacing is preserved of not.  But how do I effect the same thing within AJAX / alerts, etc?

1. If I write a simple Java script outside of Salesforce (no AJAX, etc.) that writes some text in an alert, prompt or confirm with double spaces between words or leading spaces before a line it prints out exactly as provided, e.g. alert("     Fred") produces "     Fred".  If I do the same inside S-Control the double spaces become single and the leading spaces disappear, i.e. I get "Fred".  There's a HTML tag <PRE> </PRE> that sets whether spacing is preserved of not.  But how do I effect the same thing within AJAX / alerts, etc?

Since Winter 06 Custom Objects now support RecordType.  Alas, I couldn't dump this field out using Data Connector or Salesforce's Data Loader.

But I fixed that by upgrade to Ron's January 2006 release of SF Data Connector 6.16, with SForceOffice 3 and under Options, Advanced, Specify Default Server URL on Startup: "https://www.salesforce.com/services/Soap/c/7.0".

So now I can dump, but it turns out only 153 records is all I can get out :(.  It seems I need 7.0 to get to RecordType BUT when 7.0 is used you can't dump more than 153 records.  When you do a query it says first 50 of 1000, 100 of 1000, 150 of 1000 and ends at 153.  This looks like the same problem as reported by The Fox in "Sforce Excel Connector issue?" on 28/02 (or 02/28 if you prefer).

What I want to do is change the RecordType for a bunch of records.  No problems, I'll do 153 at a time.

Wrong.  Any attempt to change th RecordType field results in the dreaded "Row Update Failed insufficient access rights on cross-reference id"

I'm stuck at this point.  Any insights would be greatly appreciated.

I went back to Salesforce Data Loader and upgraded to the currently supported level (via Salesforce Data Management to 7.0).  The upgrade provides visibility to the RecordType field.  Note: SFDL allows me to dump all the records, not just the fist 153.  Alas, I get the same "insufficient access rights on cross-reference id" if I try to change the RecordType.

In summary I have two problems:

1. Sfoce Excel Connector wont load more that 153 record if using endpoint 7.0

2. Neither utility will allow me to change the Recordtype - both report "insufficient access rights on cross-reference id".

All help as always will be appreciated.

Thanks.

Message Edited by mikeol on 03-04-200603:58 AM

Is there a way, particularly for testing, to limit the number of records returned by a query to the first "n" records (as well as the normal filtering such as field equals xxx)?

Many thanks.

I'm finding that the sforce Excel Connector (5.51) is loading date fields from Salesforce records to Excel inconsistently.

Dates which display in Salesforce such as 3/02/2005 (3rd of Feb to us Aussies) when loaded via the Connector display as 2/03/2005 in Excel with a cell type of "v" for value. Others such as 25/10/2004 when loaded display as 25/10/2004 in Excel with a cell type of "l" for literal.

The cells are set with a format of Date, Locale: English (Australia). Note: Users in my company have an sforce locale of English (Australia).

It seems that any date that could be interpreted as mm/dd is loaded that way as a true date with those that can't be loaded as literals.

I have just notice this and am part way through migrating some changes.

Thanks in advance for your assistance.

Michael

Salesforce does not allow us to define fields that can be used as a chronological log.  We have text that can be altered or cleared and that's it.  The following code is my effort at creating a "logging field".  I've not written an scontrol with any more than alerts and prompts before and discovered quite a few things on the way.  Additionally there are other issues that I've yet to resolve.  I hope this entry is helpful to others and am also hoping some of you can see better ways to do this.

====================================

In a nutshell what I want to do is to front end the way users can edit a Salesforce Long Text Area field such that they can only append to the text already in the field and that users initial and date of update are included when they do add to the log.

Salesforce allows us to give users field level-security of no access (not visible), read only access (visible and read-only) or update access (visible and not read-only) to a field.   Page Layouts allow us to reduce this through the Salesforce front end.  Someone with update access as defined in field level-security may have no access through the page layout if the field is not available.  They can still update the field though other means such as API and Ron Hess' SF Connector is an easy example.

The code below manages access via page layouts only.  So bear in mind that users who you want to append info to the field will have the ability to alter or clear the field through the API.  Luckily most users are content to only use Salesforce via the page layout interface.


To create an "append only" field users are given update access to the field, read-only access in their page layout and a button on the page-layout which will drive an Scontrol which will allow them to make an entry which the Scontrol appends to the field.  The Scontrol actually puts the User_Alias (in our site we use this to set the user's initials) and the date in front of the appended text entered by the user and then stuffs all this into the front of the existing field contents.  This gives a reverse chronological log, where the most recent entry is at the top.

Some experiences I learnt on the way.  Some fixed, some remaining a pain for which I have no answers:
1. IE 7.0 users must enable native XMLHTTP.  Under "Help & Training" search for "What Internet Explorer browser settings are optimum for salesforce.com?" to get the full list of things that must be set to make IE work.
2.Firefox prior to 2.0 generates a console error on textareas if you use alerts for debugging.  Because it's on the console users won't see it.  Refer notes at lines 213-215
3. I gather you load a JavaScript variable to a HTML page using document.write (line 233).  The fact that all carriage return/line fees are stripped when you do this was a bit of surprise.  So in line 232 they are all changed to HTML <BR /> tags.
4. As this code is a single field update it made more sense to do this as a small pop-up where the user would launch the scontrol to do the append, add their note and exit.  The underlying page would typically be the full record display.  I gather as salesforce.com launches the S-Control this restricts the S-Controls ability to control the window.  (If the code was launched from a web link, you can define some things like screen size.  But if you call the code from a Hypertext you can't do this).
4.1 In IE you get a pop-up size screen.  In Firefox you get either a full screen or a new tab.  I want a pop-up size in both browsers.  Salesforce doesn't allow me to define this as part of a Hypertext launch so I resize once running.    Lines 99-115 make the screen max, position towards the middle and shrink it back.  Seems a bit of a kludge but gets the job done.  Firefox alas, positions in the top left corner regardless.  In Firefox options, you must set "New pages should be opened in a new window" under Tools, Options..., Tabs.  IE 6 and earlier don't support tabs.  Presumably IE 7 has a similar option of opening in new window/new tab.
4.2 I'd really rather not have the Menu Bar and Address Bar(s) showing in the pop up.  My understanding is that you can ask for them not to show if you do the Window.Open.  But as Salesforce.com launches the Window we don't get to choose :(
4.3 When the user hits Save or Cancel the Scontrol updates or doesn't update the field as requested, but then closes the pop-up.  This works fine in Firefox and IE6.  Alas IE 7 asks the user to confirm the window close.  Again, because we didn't open the window I don't believe there's a way to avoid this.
4.4 No this bit is really interesting.  I wanted to call this S-Control from a hypertext field, so that I could define a button and position it next to the field displayed on the page-layout.  To get the address of the S-Control I defined a web-link to it so I could start testing earlier (takes time to define a button) by using the properties right click and besides I have always started this way.  So initially I had the hypertext working but was a bit disappointed because I was always getting a display with the left hand frame in it - search, recent items, etc.).  Also the pop-up could only be closed by closing the window (red-x).  I couldn't programatically close on the user hitting Save or Cancel.  Oh well, something I had to live with.  At some stage something broke and when I hit the button Salesforce said my address was invalid.  Strange, I hadn't changed it.  Instead of looking up the address again from the weblink I used SF Connector to list the S-Controls ID.  When I plugged its address into the Hypertext I got a pop-up without the left hand pane.   Even better, window.close() now worked. Yeah!

I can't fit the above and the code in to a single post to the discussion board so I'll add the code first.

====

Message Edited by mikeol on 03-31-2007 07:42 PM

From an SControl using AJAX how can I discover the label name for a given field (e.g. The external label name for the API field "Contract_PO__c" might be Customer PO.  How do I discover this?

I'd like a formula that takes a supplied date and returns a date which will be exactly 3 years - 1 day into the future, i.e. last day of a 3 year agreement.  For example 1 Jan 2007 > 31 Dec 2010.  In Excel I can have date made of component day or month values of 0 or less but still get a valid date, i.e. DATE(2007+3,1,1-1) which is DATE(2010,1,0) and equals 31/12/2009.

Thanks for your help.

Salesforce does not allow us to define fields that can be used as a chronological log.  We have text that can be altered or cleared and that's it.  The following code is my effort at creating a "logging field".  I've not written an scontrol with any more than alerts and prompts before and discovered quite a few things on the way.  Additionally there are other issues that I've yet to resolve.  I hope this entry is helpful to others and am also hoping some of you can see better ways to do this.

====================================

In a nutshell what I want to do is to front end the way users can edit a Salesforce Long Text Area field such that they can only append to the text already in the field and that users initial and date of update are included when they do add to the log.

Salesforce allows us to give users field level-security of no access (not visible), read only access (visible and read-only) or update access (visible and not read-only) to a field.   Page Layouts allow us to reduce this through the Salesforce front end.  Someone with update access as defined in field level-security may have no access through the page layout if the field is not available.  They can still update the field though other means such as API and Ron Hess' SF Connector is an easy example.

The code below manages access via page layouts only.  So bear in mind that users who you want to append info to the field will have the ability to alter or clear the field through the API.  Luckily most users are content to only use Salesforce via the page layout interface.


To create an "append only" field users are given update access to the field, read-only access in their page layout and a button on the page-layout which will drive an Scontrol which will allow them to make an entry which the Scontrol appends to the field.  The Scontrol actually puts the User_Alias (in our site we use this to set the user's initials) and the date in front of the appended text entered by the user and then stuffs all this into the front of the existing field contents.  This gives a reverse chronological log, where the most recent entry is at the top.

Some experiences I learnt on the way.  Some fixed, some remaining a pain for which I have no answers:
1. IE 7.0 users must enable native XMLHTTP.  Under "Help & Training" search for "What Internet Explorer browser settings are optimum for salesforce.com?" to get the full list of things that must be set to make IE work.
2.Firefox prior to 2.0 generates a console error on textareas if you use alerts for debugging.  Because it's on the console users won't see it.  Refer notes at lines 213-215
3. I gather you load a JavaScript variable to a HTML page using document.write (line 233).  The fact that all carriage return/line fees are stripped when you do this was a bit of surprise.  So in line 232 they are all changed to HTML <BR /> tags.
4. As this code is a single field update it made more sense to do this as a small pop-up where the user would launch the scontrol to do the append, add their note and exit.  The underlying page would typically be the full record display.  I gather as salesforce.com launches the S-Control this restricts the S-Controls ability to control the window.  (If the code was launched from a web link, you can define some things like screen size.  But if you call the code from a Hypertext you can't do this).
4.1 In IE you get a pop-up size screen.  In Firefox you get either a full screen or a new tab.  I want a pop-up size in both browsers.  Salesforce doesn't allow me to define this as part of a Hypertext launch so I resize once running.    Lines 99-115 make the screen max, position towards the middle and shrink it back.  Seems a bit of a kludge but gets the job done.  Firefox alas, positions in the top left corner regardless.  In Firefox options, you must set "New pages should be opened in a new window" under Tools, Options..., Tabs.  IE 6 and earlier don't support tabs.  Presumably IE 7 has a similar option of opening in new window/new tab.
4.2 I'd really rather not have the Menu Bar and Address Bar(s) showing in the pop up.  My understanding is that you can ask for them not to show if you do the Window.Open.  But as Salesforce.com launches the Window we don't get to choose :(
4.3 When the user hits Save or Cancel the Scontrol updates or doesn't update the field as requested, but then closes the pop-up.  This works fine in Firefox and IE6.  Alas IE 7 asks the user to confirm the window close.  Again, because we didn't open the window I don't believe there's a way to avoid this.
4.4 No this bit is really interesting.  I wanted to call this S-Control from a hypertext field, so that I could define a button and position it next to the field displayed on the page-layout.  To get the address of the S-Control I defined a web-link to it so I could start testing earlier (takes time to define a button) by using the properties right click and besides I have always started this way.  So initially I had the hypertext working but was a bit disappointed because I was always getting a display with the left hand frame in it - search, recent items, etc.).  Also the pop-up could only be closed by closing the window (red-x).  I couldn't programatically close on the user hitting Save or Cancel.  Oh well, something I had to live with.  At some stage something broke and when I hit the button Salesforce said my address was invalid.  Strange, I hadn't changed it.  Instead of looking up the address again from the weblink I used SF Connector to list the S-Controls ID.  When I plugged its address into the Hypertext I got a pop-up without the left hand pane.   Even better, window.close() now worked. Yeah!

I can't fit the above and the code in to a single post to the discussion board so I'll add the code first.

====

Message Edited by mikeol on 03-31-2007 07:42 PM

Since Winter 06 Custom Objects now support RecordType.  Alas, I couldn't dump this field out using Data Connector or Salesforce's Data Loader.

But I fixed that by upgrade to Ron's January 2006 release of SF Data Connector 6.16, with SForceOffice 3 and under Options, Advanced, Specify Default Server URL on Startup: "https://www.salesforce.com/services/Soap/c/7.0".

So now I can dump, but it turns out only 153 records is all I can get out :(.  It seems I need 7.0 to get to RecordType BUT when 7.0 is used you can't dump more than 153 records.  When you do a query it says first 50 of 1000, 100 of 1000, 150 of 1000 and ends at 153.  This looks like the same problem as reported by The Fox in "Sforce Excel Connector issue?" on 28/02 (or 02/28 if you prefer).

What I want to do is change the RecordType for a bunch of records.  No problems, I'll do 153 at a time.

Wrong.  Any attempt to change th RecordType field results in the dreaded "Row Update Failed insufficient access rights on cross-reference id"

I'm stuck at this point.  Any insights would be greatly appreciated.

I went back to Salesforce Data Loader and upgraded to the currently supported level (via Salesforce Data Management to 7.0).  The upgrade provides visibility to the RecordType field.  Note: SFDL allows me to dump all the records, not just the fist 153.  Alas, I get the same "insufficient access rights on cross-reference id" if I try to change the RecordType.

In summary I have two problems:

1. Sfoce Excel Connector wont load more that 153 record if using endpoint 7.0

2. Neither utility will allow me to change the Recordtype - both report "insufficient access rights on cross-reference id".

All help as always will be appreciated.

Thanks.

Message Edited by mikeol on 03-04-200603:58 AM

Hi,

I am making this query in the sforce explorer
Select AmountInherited, CreatedById, CreatedDate, CurrencyIsoCode, ForecastCategoryInherited, Id, LastModifiedById, LastModifiedDate, OpportunityId, OutOfDate, OverrideAmount, OverrideComment, OverrideForecastCategory, OverridePeriodId, OverrideQuantity, OwnerId, PeriodInherited, QuantityInherited, SystemModstamp from OpportunityOverride where LastModifiedDate>2006-02-20T00:00:00.000Z and I have 216 results

The same one in sforce Excel Connector 153 rows return (sforce excel connector 6.15)
For both the 7.0 Endpoint otherwise this table is not avalaible

Hope you can provide help

Regards

Is there a way, particularly for testing, to limit the number of records returned by a query to the first "n" records (as well as the normal filtering such as field equals xxx)?

Many thanks.

I'm finding that the sforce Excel Connector (5.51) is loading date fields from Salesforce records to Excel inconsistently.

Dates which display in Salesforce such as 3/02/2005 (3rd of Feb to us Aussies) when loaded via the Connector display as 2/03/2005 in Excel with a cell type of "v" for value. Others such as 25/10/2004 when loaded display as 25/10/2004 in Excel with a cell type of "l" for literal.

The cells are set with a format of Date, Locale: English (Australia). Note: Users in my company have an sforce locale of English (Australia).

It seems that any date that could be interpreted as mm/dd is loaded that way as a true date with those that can't be loaded as literals.

I have just notice this and am part way through migrating some changes.

Thanks in advance for your assistance.

Michael