Numerical Formula Field not saving a value after closing the record.

  • 1
  • 1
  • Question
  • Updated 1 month ago
  • Answered
  • (Edited)
Background: I am creating an easy way for my office to record the amount of supplies we have in the office at any time. To do this, i have created two tables that will work together through a relationship. The first table i made is simply called "Supply Records" and is meant to simply have the name of a certain supply, as well as the quantity that we have of that supply at the start of the current month.


The Updated quantity field is a formula field used for later on....

The other table i made is the Supply Management table and is used to track individual supply orders. I have it set so that this table is a child to the Supply Record Table, and when someone intends to add a management record they will link the record to the correct supply in the supply record table, this will pull the current quantity of that supply in the office and from there the user will fill out the information in the form to indicate an added or loss amount of that supply.



Total Quantity in Supply Management is obtained from Supply Record and will use either the Total Quantity field or the Updated Quantity field for its value depending on the date that the order is created.

Now, my problem lies with the Current Quantity formula field, basically adds the value of the Quantity Received to the total quantity, and then subtracts the value of quantity used. This total is the most important value as it is meant to update how much of a supply we have left after any order, however after the supply management record is saved, the value in the Current QUantity field is blanked out and holds no value (even though it DID display a value while editing). 

So, my question is, why is this happening? one thing to also note is that sometimes the value WILL be saved after saving, but once you back out onto the main page for the table the value will disappear  again. 
Photo of Wayne Major

Wayne Major

  • 298 Points 250 badge 2x thumb

Posted 3 months ago

  • 1
  • 1
What's the formula?
Nothing obvious there. I would chase the bug upstream - what are the formulas/values in those three fields? There's a dependency in there somewhere that's either getting confused about a field to field relationship or else blanking out on you at some point for some reason.
Photo of Wayne Major

Wayne Major

  • 298 Points 250 badge 2x thumb


The formula for the field Total Quantity is as follows:
var date FirstDayOfTheMonth = FirstDayOfMonth(Today()) ;
If(Today() = $FirstDayOfTheMonth or IsNull([Supply - Updated Quantity]), [Supply - Total Quantity] , [Supply - Updated Quantity] )
// If an order is being made at the beginning of the month, than the quantity will use the beginning month value, if the order is being made at any other date or if there is no updated quantity yet, the initial quantity will be used 
The quantity Received as well as quantity used are just input fields that have no formula.

The [Supply - Updated Quantity] field resides in the related table called Supply Record and will remain null until it is updated by a order in Supply Management.

[Supply - Updated Quantity] has a formula that looks like this:

[Order - Current Quantity]
Starting out, this value should be null since it has yet to be updated, as a falisafe, the [Total Quantity (Beginning of the Month)] field is used in its place.  Ive tried to cover all areas so that even if a field is left blank or null, there should always be some number being returned. The thing that really confuses me is that all my formulas are working as expected yet occasionally the [Current Quantity] field will simply not save the data it receives thorugh its formula, it WILL display it (sometimes), but if you exit the order or save it, and then go back into it the data for that field only will disappear and break everything.
occasionally the [Current Quantity] field will simply not save the data it receives thorugh its formula, it WILL display it (sometimes), but if you exit the order or save it, and then go back into it the data for that field only will disappear and break everything

Yeah. This is the classic behavior of a circular relationship.
Photo of Wayne Major

Wayne Major

  • 298 Points 250 badge 2x thumb
I redid the entire project and eventually got the entire thing to work properly without creating a circular relationship.
Ha, sure, "rebuild it from the ground up" is a good troubleshooting technique.

Glad you got it figured out!