Forums Ultimate UI for Windows Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Nathan Green
2060
Formula's with #Ref error when columns used contain "/"
posted
Hi,I've got a form that I'm using as a generic report viewer.  It contains a wingrid.  Each report that uses this form has different summaries and some have custom columns to give row calculations.In some reports the columns are defined by data the user has entered in editor screens within the application.  The application has Person records and Process records.  The user can allocate a % of time of that person to the Process.  For example Person "Bob" has 10% of his time allocated to Process "Sales Order Input".  One of the reports required will create dynamic field headers based on the Process name, and then a custom column is added to give a total for that row. For example
Name Sales Order Input Total Utilisation
Bob 10% 10%
 The more processes added the more columns that will appear in this report regardless of whether Bob is allocated or not. 
Name Sales Order Input Build Widget Total Utilisation
Bob 10% 0% 10%
 The formula for the Total Utilisation is dynamically created and will look like [Sales Order Input] + [Build Widget] However should the user decide to add a process called “Write/Raise PO” with the formula reading [Sales Order Input] + [Build Widget] + [Write/Raise PO] then a #Ref error will be displayed in the Total Utilisation column.  The same thing happens in the summaries where “Write/Raise PO” is used in the calculation.   If I replace the “/” with “|” then the calculation works fine.  When I run the program and use the control’s ∑ Sum it creates the summary without an error. I don’t want to have to replace characters in the user’s entries.   Is there a way around this?  Is it possible to use the index of the column instead of the key in formulas? If not are there any other characters that will cause the same problem? Many thanks Nathan

 

  • 469350
    Offline posted

    Hi,

    I assume you are using UltraCalcManager formulas here and that's why it's not working.

    In that case, you will have to alter the formula string in order to get this to work. The "/" character is a reserved character used for separating elements of a formula reference. So the only way to make this work is to escape it.

    So this:

    “[Write/Raise PO]”

    Would have to be changed to this:

    “[Write\/Raise PO]”

    The forward slash here is an escape code. In C#, this actually get even more complicated, since the forward slash is also a C# escape code and in order to get a literal forward slash into a string, you have to write it like this:

    “[Write\\/Raise PO]”

    or

    @“[Write\/Raise PO]”

    Personally, I think what I would do is change the Key of the column and remove the "/" character. If this is a bound column, this would, of course, have to be done on the data source, not in the grid.

    Then you can set the column.Header.Caption to include the "/" so the user still sees it on the grid, but not in the formulas.

  • 71886
    Offline posted

    Hello Nathan,

    I was not able to reproduce this scenario, so I attached my sample to this post. As to your question: yes, it is possible to use the column index. It is actually not so recommended to do so, especially if the user is able to move the columns, hide them or perform an operation similar to these. Therefore, the column index is a matter of change, so it is best to use the key of the column.

    Please feel free to let me know if I misunderstood you or if you have any other questions.

    GridColumnsSum.zip