Formsettings on Invoice Form, FormPreferencesService

[former_member184566]

Hi Everyone

Im trying to use formsettingspreference to set what columns will be visible in the matrix in the invoice form. The below code works only if sbo is closed, once you log on you can see the changes, i'm doing it through the DI API.

My problem is that i'm not sure which values to use for the columns id's. Below i use 24 which is the columnid of "warehouse column" in the front end of sbo, but when i run the code it makes "Gross Profit (LC)" visible and not the warehouse column. Which number must i use? Where can i get all the columns id numbers?

Has anyone used this new functionality yet? What numbers must i use for column id?

Dim FormSettingsService As SAPbobsCOM.FormPreferencesService

Dim ColumnSettingsData As SAPbobsCOM.ColumnsPreferences

Dim ColumnSettingsPara As SAPbobsCOM.ColumnsPreferencesParams

oCompanyService = oCompany.GetCompanyService

FormSettingsService = oCompanyService.GetBusinessService(SAPbobsCOM.ServiceTypes.FormPreferencesService)

ColumnSettingsPara = FormSettingsService.GetDataInterface(SAPbobsCOM.FormPreferencesServiceDataInterfaces.fpsdiColumnsPreferencesParams)

ColumnSettingsPara.FormID = "133"

ColumnSettingsPara.User = 1

ColumnSettingsData = FormSettingsService.GetColumnsPreferences(ColumnSettingsPara)

ColumnSettingsData.Item(24).EditableInForm = SAPbobsCOM.BoYesNoEnum.tYES

ColumnSettingsData.Item(24).VisibleInForm = SAPbobsCOM.BoYesNoEnum.tYES

FormSettingsService.UpdateColumnsPreferences(ColumnSettingsPara, ColumnSettingsData)

Thanks in advance

[Former Member · Accepted solution]

I have just tried this object for the first time and have to say I found the choice of index value to the item collection to be rather strange and not very useful.

Basically where you have used (24) as the index, which is the logical choice, SAP have instead chosen it to be indexed by an internal row counter. In my database this lead to column "24" actually being index 110.

A loop through the columnsettingsdata can easily find the correct column, but is highly inefficient.

For lngCount = 0 To ColumnSettingsData.Count - 1
    If ColumnSettingsData.Item(lngCount).Column = "24" Then
        ColumnSettingsData.Item(lngCount).EditableInForm = SAPbobsCOM.BoYesNoEnum.tNO
        ColumnSettingsData.Item(lngCount).VisibleInForm = SAPbobsCOM.BoYesNoEnum.tYES
        Exit For
    End If
Next

The help file lists the index value as a variant, but it won't accept strings. Personally I think it should accept the columnUID as a string value as this would be the most logical way to access it for a B1 developer.

I have still to try out the XML interface, hopefully that will be a lot easier to use.

John.

[former_member184566 · Accepted solution]

Hi John

Thanks a lot. Once again you have proven to be much more than helpfull. If only i could give you more than ten points as i would of never thought of what you just did. It works great.

What my next approach was to see if there is a table that stores all this information, i found a table that stores the fields information. I tried this query in hoping to find a match between the required index needed versus columnID

SELECT *

FROM [CPRF]

WHERE FormID=133 and usersign=1 and VisInForm='Y' and itemID='38'

I find no MATCH

I agree fully with you that they should of used columnid and that this is highly inefficient. I dont see why anyone would make it like this.

Please let me know if you find anything in the XML that would make life easier.

THANKS A LOT JOHN

[Former Member · Accepted solution]

Hmz... Same problem here...

I've put a message to SAP in on this, I can't think this is the functionality the developers intended, maybe it's just a little bug that cpould be solved in one of the next patchlevels.

Louis, the table you're describing (CPRF) only holds records for the setting you've changed. If a certain field is visible by standard, it will only be visible in this table if you've changed it's setting. I.e. if the user with userid 1 (manager) has hidden it. (or you did by the FormPreferencesService object)

Hopefully John can come up with something on the XML...

[former_member184566 · Accepted solution]

Hi R.Schwachofer

I do think it is a bit silly and i hope that this wasn't the way they would like us to use it.

About the table, surely there then must be a table where we can see all the columns and it's possible values???

Do you know of such a table??

Thanks for the reply