QuickBase Resources - Formulahttp://quickbase.intuit.com/developer/taxonomy/term/116/0
enHow can I discount or override my list prices when entering a customer’s order?http://quickbase.intuit.com/developer/knowledge-base/how-can-i-discount-or-override-my-list-prices-when-entering-customer%E2%80%99s-order
<p>Let’s say you have a table for Products, another table for
Line Items, and a <a href="http://www.quickbase.com/user-assistance/#creating_a_relationship.html" rel="nofollow">relationship</a>
between the two where the product table is the master. Let’s also assume you
have a <a href="http://www.quickbase.com/user-assistance/#create_lookup_field.html" rel="nofollow">lookup
field</a> called List Price which displays the price of the product on the line
item.</p>
<p>First you’ll want to add a Numeric – Currency field called
Price Override to the Line Items table. This will allow users to enter a
different price when adding a line item.</p>
<p>Next, create a Formula – Numeric field called Price with
this formula:</p><p>&nbsp;</p><p>If([Price Override]&lt;&gt;0,[Price Override],[List Price])</p><p>&nbsp;</p><p>The Price field will display the List Price if there is no
override, otherwise it will display the Price Override.</p>
<p><strong>Some helpful tips:</strong><strong>&nbsp;</strong></p>
<ul><li>Some companies require a manager to enter a
price override. To implement that restriction, &nbsp;edit the properties of the Price Override
field, check “Restrict access by role”, and set the field access to View for each
role that is not authorized to enter overrides.</li><li>You also may want to customize your line item
form to set the List Price and Price Override fields to only display when
editing or adding a record, and the Price field to only display when viewing a
record. This can simplify entering line items for your users, as t1he only price
likely to matter once they’ve saved the line item is what the customer will be
charged.</li></ul><p>&nbsp;
<img src="http://quickbase.intuit.com/developer/sites/default/files/images/for%20price%20override%20kb%20article.png" alt="" width="537" height="210" /></p>http://quickbase.intuit.com/developer/knowledge-base/how-can-i-discount-or-override-my-list-prices-when-entering-customer%E2%80%99s-order#commentsFormulaformula price order inventoryThu, 11 Sep 2014 13:53:29 +0000bcafferelli3335 at http://quickbase.intuit.com/developerHow do I create an automatically increasing sequential number or alpha-numeric string?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-automatically-increasing-sequential-number-or-alpha-numeric-string
<p>If you create a formula numeric type of field, you can reference the built in field Record ID # to increment your number automatically. If you're not familiar with creating new fields, please see our <a href="https://www.quickbase.com/user-assistance/default.html#adding_a_field.html" rel="nofollow">Add a New Field</a> help topic.</p><p>&nbsp;</p><p>
Once you've created your formula numeric field, your formula would look something like this.
</p><p>
[Record ID #] + 100
</p><p>This formula would take the Record ID # and add 100 to it for each new record. So if the Record ID # is 1, the formula numeric field would return 101.
</p><p>If your sequential number needs to include letters, create a formula text field instead of a formula numeric field. Your formula might look something like this.
</p><p>
[Record ID #] + 100 &amp; " " &amp; "Support"
</p><p>If the Record ID # is 1, the formula text field would return 101 Support.</p>http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-automatically-increasing-sequential-number-or-alpha-numeric-string#commentsFormulaalpaautoautomaticallyincreasingletterlettersnumbernumberingnumbersrecord ID #reocrd IDsequentialstringstringsTue, 23 Jun 2009 13:31:22 +0000quickbase2289 at http://quickbase.intuit.com/developerHow do I show only the most recent comment from an append field in my report?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-show-only-most-recent-comment-append-field-my-report
<p>Append fields can become quite large and contain too much text to display in a report easily. You can use a formula text field to display only the most recent value from your append field and then display the formula text field in your reports instead of the append field. </p>
<p>1. Create a Formula - Text field. To learn how, please see <a href="https://www.quickbase.com/user-assistance/adding_a_field.html">our help topic</a>.</p>
<p>2. In the field's formula box, enter in the following formula, making sure to replace "AppendTightField" with the name of your append field, exactly as it is spelled and punctuated in QuickBase.</p>
<p>NotLeft(Right([AppendTightField],"]"),1)</p>
<p>3. In your reports, remove the append field as a column and replace it with the Formula Text field. To learn more, please see our <a href="https://www.quickbase.com/user-assistance/edit_view_5_columns.html">Columns to Display</a> help topic.</p>
<p>Your report is now much easier to read now with only the most recent comment visible.</p>
http://quickbase.intuit.com/developer/knowledge-base/how-do-i-show-only-most-recent-comment-append-field-my-report#commentsFormulaappendappend onlycommentsfieldsformulaslastmost recentnotesreportsTue, 02 Jun 2009 21:38:08 +0000pgross2288 at http://quickbase.intuit.com/developerHow can I create a report that displays all the records based on a date field that equals a certain month and changes dynamically with the date?http://quickbase.intuit.com/developer/knowledge-base/how-can-i-create-report-displays-all-records-based-date-field-equals-certain-month-and-chang
<p>By using a custom formula, you can create a report that displays the records where a date field in the record is equal to the current month (or previous or next month). If you want to base a view on the [Date Created] field value, then create a new report, choose Table report, and within the Filters section select the right button to enable filtering. Next, select &lt;Custom Column&gt;' 'is equal to' 'True.' This will return only the records where the formula you enter returns True.</p>
<p>Now you need to create the custom column. Under the 'Columns' section in the Report Builder, click 'Custom columns'. Once you've selected the columns you want to appear on the report, check the box 'Define a calculated column.' For Type, select 'Checkbox' and enter a Label of your choice to name this new column. The formula you enter in the box will determine the records that are evaluated to true, and subsequently returned. My example uses the [Date Created] value and returns all the records that were created in the current month, which we'll say is October. The formula I enter is:</p>
<p>If(Month(ToDate([Date Created]))=Month(ToDate(Now())), true) </p>
<p>This formula returns true only if the current month is equal to the month from the date created field. If you want to return records for the next month, you can modify the formula to add a month:</p>
<p>If(Month(ToDate([Date Created]))=(Month(ToDate(Now()))+1), true) </p>
<p>or the previous month:</p>
<p>If(Month(ToDate([Date Created]))=(Month(ToDate(Now()))-1), true) </p>
<p>Note: These formulas do NOT take year into account, so any record with the month equal to the current month, regardless of the year, will be returned. If you wish to match the year as well, you'll need to add a statement such as "AND (Year(ToDate([Date Created]))=(Year(ToDate(Now())))"</p>
http://quickbase.intuit.com/developer/knowledge-base/how-can-i-create-report-displays-all-records-based-date-field-equals-certain-month-and-chang#commentsFormulacurrent monthDatedynamic viewformulamonthThu, 05 Feb 2009 22:16:28 +0000quickbase2185 at http://quickbase.intuit.com/developerHow come the totals and averages of a numeric formula field don't follow the formula?http://quickbase.intuit.com/developer/knowledge-base/why-dont-totals-and-averages-numeric-formula-field-follow-formula
<p>The total value at the bottom of a column is the total of all values above it in the report. The same is true of the average value at the bottom of a report. The average value is the average of all the values in the column above it. The totals and averages at the bottom of a formula field do not necessarily relate to the totals and averages of fields that make up the formula in the way that the formula specifies. For example imagine ten rows and three fields. All cells have the value one in them. Totals are turned on for all three columns. Imagine that the third column is a formula field that divides the first column by the second column. The totals at the bottom of all three columns will display "10". However if you divide the total of column one by the total of column two you get the number one not the number 10! However in this case the average at the bottom of each column is one, which does follow the formula, but this is only a coincidence.</p>
<p>Here's another example:</p>
<p> 10 5 2<br />
100 1 100<br />
------------<br />
110 6 102 Totals<br />
55 3 51 Averages</p>
<p>In the above example the third column is a formula numeric field that divides the first column by the second column. Notice that in the Totals and Averages rows they represent the totals and averages of the numbers in the column above. However in the Totals row if you divide 110 by 6 you don't get 102 or even 51! And if you divide 55 by 3 in the Averages row you don't get 51 or even 102.</p>
http://quickbase.intuit.com/developer/knowledge-base/why-dont-totals-and-averages-numeric-formula-field-follow-formula#commentsField TypesFormulaNumericaveragefieldformulanumbernumerictotalsThu, 05 Feb 2009 22:16:10 +0000quickbase2073 at http://quickbase.intuit.com/developerHow do I make a formula date field called "Completion Date" that captures the date when a multiple choice field called "Status" is set to a particular value like "Complete"?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-make-formula-date-field-called-completion-date-captures-date-when-multiple-choice-f
<p>The best way to do this is to flip the problem around on its head.</p>
<p>1) Instead of making the date field a formula field, make it a plain date field.</p>
<p>2) Then remove the choice of "Complete" from your multiple choice "Status" field.</p>
<p>3) Create a formula text field called "Computed Status".</p>
<p>4) Put the following formula into the formula property of the "Computed Status" field.</p>
<p>if(isnull([Completion Date]), [Status], "Complete")</p>
<p>5) Now in all your views and display record forms replace the "Status" field with the "Computed Status" field.</p>
<p>6) On custom forms you can use alternate label text to relabel the "Computed Status" field back to "Status".</p>
<p>Your users will now mark a record as "Complete" by simply entering a date in the "Completion Date" field. A blank date in the "Completion Date" field means the record's status is not complete.</p>
http://quickbase.intuit.com/developer/knowledge-base/how-do-i-make-formula-date-field-called-completion-date-captures-date-when-multiple-choice-f#commentsField TypesFormulaTextcapturecombocompletionDatedrop downfieldmultimultiple choicesnap shotsnapshotstatusThu, 05 Feb 2009 22:16:07 +0000quickbase2051 at http://quickbase.intuit.com/developerHow do I create a field that displays the hours and minutes portion of the built-in timestamp fields "Date Created" and "Date Modified"?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-field-displays-hours-and-minutes-portion-built-timestamp-fields-date-created
<p>Create either a view with a "Custom Column" of type text that has the following formula:</p>
<p>Int(ToHours([Date Created]-ToTimestamp(ToDate([Date Created])))) &amp; ":" &amp; Int(Mod(ToMinutes([Date Created]-ToTimestamp(ToDate([Date Created]))), 60))</p>
<p>or a formula text field with the same formula.</p>
<p>The formula for "Date Modified" would be:</p>
<p>Int(ToHours([Date Modified]-ToTimestamp(ToDate([Date Modified])))) &amp; ":" &amp; Int(Mod(ToMinutes([Date Modified]-ToTimestamp(ToDate([Date Modified]))), 60))</p>
http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-field-displays-hours-and-minutes-portion-built-timestamp-fields-date-created#commentsField TypesFormulaTextbuilt inDatedate createdhoursminutesmodifiedtime stamptimestampThu, 05 Feb 2009 22:16:05 +0000quickbase2038 at http://quickbase.intuit.com/developerHow do I create a delete record link that can be clicked on from a report?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-delete-record-link-can-be-clicked-report
<p>Just create a Formula URL field and use the following as its formula:</p>
<p>"javascript:if(confirm ('Are you sure you want to delete this record?')){location.assign('/db/" &amp; Dbid() &amp; "?act=API_DeleteRecord&amp;rid=" &amp; [Record ID#]&amp; "&amp;rdr=" &amp; URLEncode(URLEncode(URLRoot() &amp; "db/" &amp; Dbid() &amp; "?a=q&amp;qid=7")) &amp; "')}"</p>
<p>You'll have to change the seven character "7" in the above formula to the qid of the report that you want to return to. To learn about qids please read the following:</p>
<p><a href="http://quickbase.intuit.com/developer/node/2087" title="http://quickbase.intuit.com/developer/node/2087">http://quickbase.intuit.com/developer/node/2087</a></p>
<p>NOTE: QuickBase recommends that you use application tokens when working with API calls (To learn more, please see our <a href="https://www.quickbase.com/user-assistance/app_tokens.html">Application Tokens</a> help topic).</p>
http://quickbase.intuit.com/developer/knowledge-base/how-do-i-create-delete-record-link-can-be-clicked-report#commentsFormuladeletelinkviewThu, 05 Feb 2009 22:15:56 +0000quickbase1965 at http://quickbase.intuit.com/developerHow do I easily create a recurring record or recurring tasks?http://quickbase.intuit.com/developer/knowledge-base/how-do-i-easily-create-recurring-record-or-recurring-tasks
<p>You can add a recurring record or tasks link field (which can be made to look like a button) by clicking on the link below.</p>
<p><a href="https://www.quickbase.com/db/9kaw8phg?a=dbpage&amp;pagename=recurring.html" title="https://www.quickbase.com/db/9kaw8phg?a=dbpage&amp;pagename=recurring.html">https://www.quickbase.com/db/9kaw8phg?a=dbpage&amp;pagename=recurring.html</a></p>
<p>The wizard will ask you for the table where you want this link field and then it will ask you for the date field that should be incremented to space the newly created records in time. This can be used for any table, not just tasks, to quickly allow users to create multiple copies of the same record spaced out over time.</p>
<p>When an end-user clicks the resulting link field they are prompted for how many copies they want and for what interval they want to use to space them out in time.</p>
<p>NOTE: This wizard works with application tokens (To learn more, please see our <a href="https://www.quickbase.com/user-assistance/app_tokens_wizards.html">QuickBase Add-ons and Application Tokens</a> help topic). If you want to use Application Tokens with this wizard add the following token to your application:<br />
c5gchf9bhwx6umbd2ii5zdqtb5x8<br />
If not, you can leave the application's token requirement set to disabled.</p>
<p>Note: If the recurring record contains a Numeric - Percent type field, you'll need to adjust that percent value in new records you create with the recurring button or link. QuickBase automatically divides values in a numeric percent field by 100. This feature lets you enter percentages in whole numbers. So, if you want to enter a value of 30%, you can enter 30 in this type of field, and behind the scenes QuickBase records this value in its proper numeric format, which is .3. This division also happens automatically when you create a recurring record from the original. For example, if your original record contains a field called Percent Complete, which is set to 60% (really .6), any recurring record created will contain the value .6% (really .006).</p>
http://quickbase.intuit.com/developer/knowledge-base/how-do-i-easily-create-recurring-record-or-recurring-tasks#commentsFormulaThu, 05 Feb 2009 22:15:51 +0000quickbase1947 at http://quickbase.intuit.com/developer