Forum Discussion
How to Display Blank field using custom field
- Okay I've got something that appears to work.
1. In the Resource Usage or Resource Sheet customize the Resource Text1 field with this formula and check the option to "use formula" for task and group summary rows::
IIf([Budget Cost]<>"",round([Number1],1) & "%","")
2. This macro will populate the Resource Number1 field with the desired percentage data
Sub CustomBud()
Dim Den As Single
Dim CFF As String
Den = Round(ActiveProject.ProjectSummaryTask.BudgetCost)
CFF = "iif([budget cost]<>"""",[budget cost]/" & Den & "*100,0)"
CustomFieldSetFormula FieldID:=pjCustomResourceNumber1, Formula:=CFF
CustomFieldPropertiesEx FieldID:=pjCustomResourceNumber1, Attribute:=pjFieldAttributeFormula, _
summarycalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
ViewApply Name:="resource sheet"
ViewApply Name:="Resource Usage"
End Sub
This was challenging.
John
Well, you're right in that the error occurs because you are trying to write a string ("") into a custom Number field. However, even if you change the formula to write a zero, the end result will ALWAYS be zero because the Budget Cost field is only applicable to the Project Summary task so no task field will ever have a budget cost. You cannot show a "blank" in a Number field, but you can use a Text field to show nothing.
On your second question, you can get the sum of a row of numbers by creating a custom field that is the sum of each element in the row (e.g, Number1 + Number2 + etc.). However, I think what you perhaps are asking is how to get the sum of a column of numbers (e.g. sum up the Number1 field). For the latter, you simply check the option "Calculation for task and group summary rows" and select "sum"
- Hi John
Thank you for your prompt reply. Appreciate your response.
1. I agree Budget Cost can only be applied to the Project summary. But what I have done is that I created a Table based on Resource Usage and in that table I have a Budget Cost col which displays all the Budget cost for each resource item. However some cells in this column can be blank. Then I created a custom field to calculate the percentages based on my earlier formula I showed. That's where my question is about for those blank cells I want to ignore calculating the percentage.
2. Yes I am asking for the sum of numbers in a column. Referring again to my Budget Cost col in my Resource Usage table I want to sum that column. I am aware that you can sum the summary by selecting an option if there is a group filter set where u will get a summary. In my resource usage table the display has no summary just pure cost items in every columns and the first col are the different resources names as defined in my Resource Sheet.
Hope my reply gives more clarity on my questions.
Look forward to your reply.
Rgds
KR- Okay so are you saying you created a custom field in the Resource Usage view that you are calling "budget cost" but it is not Project's Budget Cost field? If so, then that would have been good to know at the onset.
What is this "table" you created? Is it a lookup table? What is the field name (i.e. Project name not your custom name)?
A screen shot would be really helpful. Remember, I'm not inside your head so I can't see what you are doing or what you are thinking, you have to explain in in terms as you would to a third party. Maybe you can sense my frustration.
When you customize a field in a Usage view (Task or Resource), that formula applies ONLY to the resource or task data, NOT to the assignment data. You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows.dear John
My humble apologies for getting you frustrated over this issue that I am trying to explain. I will provide the screenshot as well hopefully this will help. I have create a View of my report and I will explain what I want to do.
I have a col Budget Cost which is NOT a custom field it gives all the Budget allocation as defined in the Resource Sheet. %Budget is the custom field which is now of type Text (as suggested in your earlier reply so that I can display blank/empty cell for those cells that I do not have any budget values during my percentage calculation. This is working the way I want now.
In order to calculate % tages I need to know the total of the Budget Cost column which is $34,500 + $38,000 + $70,400 + $28,100 + $15,000 = $186,000. I am now manually inserting $186,000 in the formula in my custom field to get the %Budget figures.
My question is how can I get this col sum of Budget Cost to avoid hard-coding $186,000 in the custom field.
Lastly, your last statement "You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows." - is they any youtube video that I can watch to understand what you meant here or any article that I can read or any example that I look at and understand this.
Once again appreciate you taking a lot your time in looking at this issue I have.
Rgds
KR
