Implementing formulas for custom fields in an on-demand database

• US 7,814,052 B2
• Filed: 11/03/2006
• Issued: 10/12/2010
• Est. Priority Date: 11/03/2006
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Bezar, Eric, Jacobs, Dean, Wong, Simon, Chasman, Doug
• Primary Examiner(s): Fleurantin; Jean B.
• Assistant Examiner(s): COLAN, GIOVANNA B
• Application Number:
  US11/592,539
• Publication Number:
  US 20080126333A1
• Time in Patent Office: 1,439 Days
• Field of Search: 707/3, 707/10, 707/4, 707/2, 707/1, 707/103.R, 707/102, 707/200, 707/101, 707/610, 707/713, 707/758, 707/954, 707/957, 707/960, 707/962, 717/106, 709/223, 709/248
• US Class Current: 707/610
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/273 Asynchronous replication or reconciliation
• CPC Class Codes: G06F 16/275 Synchronous replication
• CPC Class Codes: Y10S 707/954 Relational

Abstract

The efficiency and versatility for the implementation of formulas in an on-demand database is improved. Formulas are categorized. Based at least partly on the categorization, formulas are evaluated synchronously or asynchronously. An asynchronous evaluation may be performed if a certain set of criteria is not satisfied. Asynchronous evaluations may be performed using a queue. During an asynchronous update of an object, a counter field and/or an estimate field may be used respectively for indicating the consistency of values of the object and a time when the values were consistent. The versatility of formulas is enhanced by using a formula to create a default value for a custom field when it is created and to determine whether an action is to be performed, and is enhanced by having an action define when a formula is to be updated.

Claims

1. A method of evaluating a formula in a database system, the method comprising:

receiving, at the database system, a formula defined to access data stored in one or more rows of a database table of the database system, wherein the formula is an expression that receives the accessed data as input and provides a result that is different than the input data;

evaluating, with the database system, the formula to obtain a first result based on first values of the input data stored in the one or more rows of the database table;

receiving, at the database system, a change in a first value of at least one of the input data stored in the one or more rows;

obtaining, with the database system, an updated result of the formula by;

determining whether the formula accesses input data from only one row of a the database table; and

synchronously evaluating the formula to obtain the updated result when the formula accesses input data from only one row of the database table; and

otherwise

deciding whether to evaluate the formula synchronously or asynchronously to obtain the updated result when the formula accesses input data from at least two rows of the database table, wherein the formula accesses input data from multiple rows of the database table, further comprising synchronously evaluating the multiple-row formula to obtain the updated result when the updated result of the multiple-row formula is computable with a delta value combined with the first result of the multiple-row formula.

2. The method of claim 1, wherein determining whether the formula accesses input data from only one row of a database table comprises:

retrieving data representing one or more properties of the input data.

3. The method of claim 1, further comprising storing the first result in the database system.

4. The method of claim 1, wherein synchronously evaluating the formula to obtain the updated result includes:

computing the one-row formula point-wise.

5. The method of claim 1, wherein synchronously evaluating the formula to obtain the updated result includes:

computing the one-row formula in bulk.

6. The method of claim 1, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain the updated result includes:

deciding based on an amount of input data to the formula whether to evaluate the formula synchronously or asynchronously to obtain the updated result.

7. The method of claim 1, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain the updated result includes:

deciding based on a current performance load on the database system whether to evaluate the formula synchronously or asynchronously to obtain the updated result.

8. The method of claim 1, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain the updated result includes:

deciding based on a number of synchronous evaluations previously done for a specific user or tenant whether to evaluate the formula synchronously or asynchronously to obtain the updated result.

9. The method of claim 1, wherein the result of the formula is provided as the value for a custom field of a table in the database system.

10. The method of claim 1, wherein the formula determines whether a requested action is to be performed.

11. The method of claim 1, wherein at least one of the first result and updated result is a set of textual characters.

34. The method of claim 9, further comprising:

prior to obtaining the updated result, receiving, at the database system from a user system, a request for the custom field value; and

providing, with the database system, the updated result of the formula to the user system.

35. The method of claim 1, further comprising:

storing the formula in the database system.

12. A computer readable medium storing program code for controlling a processor to perform an operation for evaluating a formula in the database system, the program code comprising code for:

receiving a formula defined to access data stored in one or more rows of a database table of the database system, wherein the formula is an expression that receives the accessed data as input and provides a result that is different than the input data;

evaluating the formula to obtain a first result based on first values of the input data stored in the one or more rows of the database table;

receiving a change in a first value of at least one of the input data stored in the one or more rows;

determining whether the formula accesses input data from only one row of the database table; and

synchronously evaluating the formula to obtain the updated result when the formula accesses input data from only one row of the database table; and

otherwise

deciding whether to evaluate the formula synchronously or asynchronously to obtain the updated result when the formula accesses input data from at least two rows of the database table, wherein the formula accesses input data from multiple rows of the database table, and wherein the program code further code for synchronously evaluating the multiple-row formula to obtain the second result of the multiple-row formula when the second result is computable with a delta value combined with the first result of the multiple-row formula.

13. The computer readable medium of claim 12, wherein determining whether the formula accesses input data from only one row of a database table comprises:

retrieving data representing one or more properties of the input data.

14. The computer readable medium of claim 12, wherein the program code further code for storing the first result in the database system.

15. The computer readable medium of claim 12, wherein synchronously evaluating the formula to obtain a second result includes:

computing the one-row formula point-wise.

16. The computer readable medium of claim 12, wherein synchronously evaluating the formula to obtain a second result includes:

computing the one-row formula in bulk.

17. The computer readable medium of claim 12, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on an amount of input data to the formula whether to evaluate the formula synchronously or asynchronously to obtain a second result.

18. The computer readable medium of claim 12, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on a current performance load on the database system whether to evaluate the formula synchronously or asynchronously to obtain a second result.

19. The computer readable medium of claim 12, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on a number of synchronous evaluations previously done for a specific user or tenant whether to evaluate the formula synchronously or asynchronously to obtain a second result.

20. The computer readable medium of claim 12, wherein the result of the formula is provided as the value for a custom field of a table in the database system.

21. The computer readable medium of claim 12, wherein the formula determines whether a requested action is to be performed.

22. The computer readable medium of claim 12, wherein at least one of the first and second results is a set of textual characters.

23. A system comprising:

a database;

at least one processor coupled with the database, the processor configured to;

receive a formula defined to access data stored in one or more rows of a database table of the database system, wherein the formula is an expression that receives the accessed data as input and provides a result that is different than the input data;

evaluate the formula to obtain a first result based on first values of the input data stored in the one or more rows of the database table;

receive a change in a first value of at least one of the input data stored in the one or more rows determine whether the formula accesses input data from only one row of the database table; and

synchronously evaluate the formula to obtain a second result when the formula accesses input data from only one row of the database table; and

otherwise

decide whether to evaluate the formula synchronously or asynchronously to obtain a second result when the formula accesses input data from at least two rows o5 the database table, wherein the formula accesses input data from multiple rows of the database table, and wherein the processor is further configured to synchronously evaluate the multiple-row formula to obtain the second result of the multiple-row formula when the second result is computable with a delta value combined with the first result of the multiple-row formula.

24. The system of claim 23, wherein determining whether the formula accesses input data from only one row of a database table comprises:

retrieving data representing one or more properties of the input data.

25. The system of claim 23, wherein the processor is further configured to store the first result in the database system.

26. The system of claim 23, wherein synchronously evaluating the formula to obtain a second result includes:

computing the one-row formula point-wise.

27. The system of claim 23, wherein synchronously evaluating the formula to obtain a second result includes:

computing the one-row formula in bulk.

28. The system of claim 23, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on an amount of input data to the formula whether to evaluate the formula synchronously or asynchronously to obtain a second result.

29. The system of claim 23, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on a current performance load on the database system whether to evaluate the formula synchronously or asynchronously to obtain a second result.

30. The system of claim 23, wherein deciding whether to evaluate the formula synchronously or asynchronously to obtain a second result includes:

deciding based on a number of synchronous evaluations previously done for a specific user or tenant whether to evaluate the formula synchronously or asynchronously to obtain a second result.

31. The system of claim 23, wherein the result of the formula is provided as the value for a custom field of a table in the database system.

32. The system of claim 23, wherein the formula determines whether a requested action is to be performed.

33. The system of claim 23, wherein at least one of the first and second results is a set of textual characters.