Functions in Formulas

Aspose.Tasks for C++ API supports evaluating functions defined as formula expression in Extended Attribute. These include calculation of Math, General, Text, and DateTime functions.

Calculation of Math Expressions

  1. Abs( number )
  2. Atn( number )
  3. Cos( number )
  4. Exp( number )
  5. Fix( number )
  6. Int( number )
  7. Log( number )
  8. Rnd( number )
  9. Sgn( number )
  10. Sin( number )
  11. Sqr( number )
  12. Tan( number )
 1void CalculateMathExpressions::EvaluateSine()
 2{
 3    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
 4    
 5    // Set formula Sin(pi/2)
 6    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"Sin(3.1415926/2)");
 7    
 8    // Print Calculated value
 9    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
10    System::Console::WriteLine(u"Sin(pi/2): {0}", System::ObjectExt::Box<System::Decimal>(task->get_ExtendedAttributes()->idx_get(0)->get_NumericValue()));
11}
12
13System::SharedPtr<Project> CalculateMathExpressions::CreateTestProjectWithCustomField()
14{
15    System::SharedPtr<Project> project = System::MakeObject<Project>();
16    System::SharedPtr<ExtendedAttributeDefinition> attr = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::CustomFieldType::Number, Aspose::Tasks::ExtendedAttributeTask::Number1, u"Sine");
17    project->get_ExtendedAttributes()->Add(attr);
18    
19    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->Add(u"Task");
20    
21    System::SharedPtr<ExtendedAttribute> a = attr->CreateExtendedAttribute();
22    task->get_ExtendedAttributes()->Add(a);
23    return project;
24}

Calculation of General Functions

The following General functions can be calculated by the API.

  1. Choose( index, choice-1, choice-2, … , choice-n|, choice-n)
  2. IIf( expr, truepart, falsepart )
  3. IsNumeric( expression)
  4. IsNull( expression )
  5. Switch( expr-1, value-1, expr-2, value-2, … , expr-n,value-n|, expr-n,value-n )
 1void CalculateGeneralFunctions::EvaluateChoose()
 2{
 3    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
 4    
 5    // Set Formula
 6    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"Choose(3, \"This is a\", \"right\", \"choice\")");
 7    
 8    // Print extended attribute value
 9    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
10    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
11}
12
13void CalculateGeneralFunctions::EvaluateIsNumeric()
14{
15    System::ArrayPtr<System::String> numericFormulas = System::MakeArray<System::String>({u"IsNumeric('AAA')", u"IsNUmeric(1)", u"IsNumeric(1<0)", u"IsNumeric(\"1.1\")", u"IsNumeric(Choose((2 + Sgn(2^-3)), 123, \"one two three\"))"});
16    
17    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
18   
19    {
20        for (System::String numericFormula : numericFormulas)
21        {
22            // Set Formula
23            project->get_ExtendedAttributes()->idx_get(0)->set_Formula(numericFormula);
24            
25            // Print extended attribute value
26            System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
27            System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
28        }
29        
30    }
31}
32
33void CalculateGeneralFunctions::EvaluateSwitch()
34{
35    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
36    
37    // Set Formula
38    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"Switch( 0 < 1, \"0 is lesser than 1\", 0 > 1, \"0 is greater than 1\")");
39    
40    // Print extended attribute value
41    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
42    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
43}
44
45System::SharedPtr<Project> CalculateGeneralFunctions::CreateTestProjectWithCustomField()
46{
47    System::SharedPtr<Project> project = System::MakeObject<Project>();
48    System::SharedPtr<ExtendedAttributeDefinition> attr = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::CustomFieldType::Text, Aspose::Tasks::ExtendedAttributeTask::Text1, u"Custom Field");
49    project->get_ExtendedAttributes()->Add(attr);
50    
51    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->Add(u"Task");
52    
53    System::SharedPtr<ExtendedAttribute> a = attr->CreateExtendedAttribute();
54    task->get_ExtendedAttributes()->Add(a);
55    return project;
56}

Calculation of Text Functions

  1. Asc( string )
  2. Chr( charcode )
  3. Format( expression, format, firstdayofweek, firstweekofyear)
  4. Instr( start,string1, string2, compare )
  5. LCase( string )
  6. Left( string, length )
  7. Len( string )
  8. LTrim( string )
  9. Mid( string, start, length )
  10. Right( string, length )
  11. RTrim( string )
  12. Space( number )
  13. StrComp( string1, string2, compare )
  14. StrConv( string, conversion, LCID )
  15. String( number, character )
  16. Trim( string )
  17. UCase( string )
 1void CalculateTextFunctions::EvaluateStrConv()
 2{
 3    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
 4    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
 5    
 6    // Set formulas and print extended attribute value
 7    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"StrConv(\"sTring and sTRINg\",3)");
 8    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
 9    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"StrConv(\"sTring and sTRINg\",1)");
10    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
11    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"StrConv(\"sTring and sTRINg\",2)");
12    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
13}
14
15void CalculateTextFunctions::EvaluateStringFunction()
16{
17    System::SharedPtr<Project> project = CreateTestProjectWithCustomField();
18    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
19    
20    // Set formulas and print extended attribute value
21    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"String(5, 40)");
22    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
23    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"String(5, \"A\")");
24    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
25    project->get_ExtendedAttributes()->idx_get(0)->set_Formula(u"String(-5, \"A\")");
26    // #Error
27    System::Console::WriteLine(task->get_ExtendedAttributes()->idx_get(0)->get_TextValue());
28}
29
30System::SharedPtr<Project> CalculateTextFunctions::CreateTestProjectWithCustomField()
31{
32    System::SharedPtr<Project> project = System::MakeObject<Project>();
33    System::SharedPtr<ExtendedAttributeDefinition> attr = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::CustomFieldType::Text, Aspose::Tasks::ExtendedAttributeTask::Text1, u"Custom Field");
34    project->get_ExtendedAttributes()->Add(attr);
35    
36    System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->Add(u"Task");
37    
38    System::SharedPtr<ExtendedAttribute> a = attr->CreateExtendedAttribute();
39    task->get_ExtendedAttributes()->Add(a);
40    return project;
41}

Calculation of Date/Time Functions

  1. CDate( expression )
  2. Date ()
  3. DateAdd( interval, number, date )
  4. DateDiff( interval, date1, date2, firstdayofweek, firstweekofyear )
  5. DatePart( interval, date, firstdayofweek, firstweekofyear)
  6. DateSerial( year, month, day )
  7. DateValue( date)
  8. Day( date)
  9. Hour( time )
  10. IsDate( expression )
  11. Minute( time)
  12. Month( date)
  13. Now ()
  14. ProjDateAdd( date, duration, calendar )
  15. ProjDateConv( expression, dateformat )
  16. ProjDateDiff( date1, date2, calendar )
  17. ProjDateSub( date, duration, calendar )
  18. ProjDateValue( expression )
  19. ProjDurConv( expression, durationunits )
  20. ProjDurValue( duration )
  21. Second( time )
  22. Time ()
  23. Timer ()
  24. TimeSerial( hour, minute, second)
  25. TimeValue( time)
  26. Weekday( date, firstdayofweek )
  27. Year( date)
 1System::SharedPtr<Project> project = CreateTestProject();
 2System::SharedPtr<Task> task = project->get_RootTask()->get_Children()->GetById(1);
 3    
 4System::SharedPtr<ExtendedAttributeDefinition> numberDefinition = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::ExtendedAttributeTask::Number1, nullptr);
 5project->get_ExtendedAttributes()->Add(numberDefinition);
 6    
 7System::SharedPtr<ExtendedAttribute> numberAttribute = numberDefinition->CreateExtendedAttribute();
 8task->get_ExtendedAttributes()->Add(numberAttribute);
 9    
10// Set ProjDateDiff formula and print extended attribute value
11numberDefinition->set_Formula(u"ProjDateDiff(\"03/23/2015\",\"03/18/2015\")");
12System::Console::WriteLine(numberAttribute->get_NumericValue());
13numberDefinition->set_Formula(u"ProjDateDiff(\"03/23/2015\",\"03/25/2015\")");
14System::Console::WriteLine(numberAttribute->get_NumericValue());
15    
16System::SharedPtr<ExtendedAttributeDefinition> dateDefinition = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::ExtendedAttributeTask::Date1, nullptr);
17project->get_ExtendedAttributes()->Add(dateDefinition);
18System::SharedPtr<ExtendedAttribute> dateAttribute = dateDefinition->CreateExtendedAttribute();
19task->get_ExtendedAttributes()->Add(dateAttribute);
20    
21System::SharedPtr<ExtendedAttributeDefinition> durationDefinition = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::ExtendedAttributeTask::Duration4, u"Custom duration field");
22project->get_ExtendedAttributes()->Add(durationDefinition);
23System::SharedPtr<ExtendedAttribute> durationAttribute = durationDefinition->CreateExtendedAttribute();
24task->get_ExtendedAttributes()->Add(durationAttribute);
25    
26System::SharedPtr<ExtendedAttributeDefinition> textDefinition = ExtendedAttributeDefinition::CreateTaskDefinition(Aspose::Tasks::ExtendedAttributeTask::Text5, u"Custom text field");
27project->get_ExtendedAttributes()->Add(textDefinition);
28System::SharedPtr<ExtendedAttribute> textAttribute = textDefinition->CreateExtendedAttribute();
29task->get_ExtendedAttributes()->Add(textAttribute);
30    
31// Set ProjDateSub formula and print extended attribute value
32dateDefinition->set_Formula(u"ProjDateSub(\"3/19/2015\", \"1d\")");
33System::Console::WriteLine(System::ObjectExt::Box<System::DateTime>(dateAttribute->get_DateValue()));
34    
35// We can set ProjDurConv formula to duration-valued attribute as well as to text-valued attribute.
36    
37// Set ProjDurConv formula to duration-valued extended attribute and print its value.
38durationDefinition->set_Formula(u"ProjDurConv([Duration], pjHours)");
39System::Console::WriteLine(System::ObjectExt::Box<Duration>(durationAttribute->get_DurationValue()));
40    
41// Set ProjDurConv formula to text-valued extended attribute and print its value.
42textDefinition->set_Formula(u"ProjDurConv([Duration], pjHours)");
43System::Console::WriteLine(textAttribute->get_TextValue());
44    
45textDefinition->set_Formula(u"ProjDurConv([Duration], pjWeeks)");
46System::Console::WriteLine(textAttribute->get_TextValue());
47    
48// Set Second formula and print entended attribute value
49numberDefinition->set_Formula(u"Second(\"4/21/2015 2:53:41 AM\")");
50System::Console::WriteLine(numberAttribute->get_NumericValue());
51    
52// Set Weekday formula and print entended attribute value
53numberDefinition->set_Formula(u"Weekday(\"24/3/2015\", 1)");
54System::Console::WriteLine(numberAttribute->get_NumericValue());
55numberDefinition->set_Formula(u"Weekday(\"24/3/2015\", 2)");
56System::Console::WriteLine(numberAttribute->get_NumericValue());
57numberDefinition->set_Formula(u"Weekday(\"24/3/2015\", 3)");
58System::Console::WriteLine(numberAttribute->get_NumericValue());
Subscribe to Aspose Product Updates

Get monthly newsletters & offers directly delivered to your mailbox.