Display last value from a table on a form

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I would like to have the value of a field in the Orders table displayed on a form when creating new orders.  The value of that field is a numerical value based on the previous order. For example, Customer 1 places an order (Order 1) and the field "Balance" has a value of 50. When Customer 1 has a second order (Order 2) I  would like to see that the previous order had a "balance" of 50. The two tables are Customers and Orders.
Thank you very much.

Photo of Alan

Alan

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
You can do this with script or using native features with lookup and summary fields. However, this is a good question to learn a couple of fundamental points about QuickBase. Most databases assume each record is independent of other records and is designed with some level of normalization (generally designed so that fields are defined  to reduce or eliminate redundancy of information within a record). So concepts like "previous record" are not inherently modeled using the features built into the database engine. You can however define your own "previous record" criteria using approaches such as (1) the child record related to the same parent of the current child record being edited or viewed with the largest [Record ID#] or (2)  the child record related to the same parent of the current child record being edited or viewed with the most recent date ([Date Created], [Date Modified], or user created [Order Date]).

Using script you would use a three step process:

(1) grab the [Related Parent] record of the current child record being edited or viewed

(2) query for the child record related to the parent record identified in step (1) and extract the relevant field (balance in your case)

(3) display the value from step (2) on the form

Using native features you would take a similar three step process (using summary and lookup fields). I will leave the details to Mark as he is the expert with using summary and lookup fields and just note here that at some level there is a sort of duality between script and native approaches.

However, there is a concurrency issue that needs to be mentioned. It may be possible in some cases where a second user could create a new record after the first users starts editing his record but before the first users saves his record. This could technically mess up what the prior balance is. If the balance field is just an indicator of how large the last order was there may be no real downside. However. if the balance is a accounting field that must be 100% accurate at all times you might have a problem on your hands that would require another solution.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I suggest simply having a section of the order record form showing all previous Orders.

Just create a Report Link field.  On the left side configure it to be [related Customer].  On the right side navigate back to your app, and then to the same field [Related Customer] on the Orders table.

Once you build that Report Link field, put it on your form and set the form properties to show the records directly on the form.  Then build a an unfiltered report with the columns that you want including that balance field and edit the form to use that Report when displaying the other orders for the same customer.  If you only want to include Orders which have a Balance >0, then edit that report to add that filer to the report.
Photo of Alan

Alan

  • 0 Points
Hi Mark. Thank you for your help. Pardon my ignorance, but where do I go to create the Report Link Field? Thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
No problem.
Settings (for that table)
Fields
Add new field
Give it a name like Report Link of Orders for this Customer and select Field Type of Report Link.