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.
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.
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.