4 Replies Latest reply on Sep 23, 2011 2:00 PM by tom_molloy

    updating fields



      updating fields


      I need to have several fields in a record updated as a result of inputting a value. Specifically when I enter a "batch" size I want the number of bottlee, caps etc to change accordingly. I have used a calculated field for this but when I enter subsequent batches of a different size the previous values do not change. I have also attempted to use Auto-enter but although I specified calculated value with'do not replace existing value' checked and unchecked nothing was calculated. In this case the fields remained blank.  

        • 1. Re: updating fields

          Can you describe what you want to do here in greater detail?

          How will a change in "batch size" change the values in the other fields?

          Why would you want a change in subsequent batches to change previous values?

          Are all these fields in the same table?

          Are all the values in one record or several?

          Both a calculation field and an auto-entered calculation may be possible here, but I suspect a design issue with your database is complicating the process for you.

          • 2. Re: updating fields

            Thanks for responding. Let me try and explain.


            Each job run has a different  batch size. The batch size determines the number of bottles, caps etc . I need. The same job can be on multiple occasions. The number of bottle etc has to be calculated on each occasion as the details of each job and batch have to be recorded. I have two tables: Job table which stores the details of of the bottle and cap type type to be used for that job. The job request table which records the batch size for a particular job. I have been trying to create an inventory "picklist' using a portal. Hope this makes sense.



            • 3. Re: updating fields

              Do you create a new record in the Job Request table for each new batch?

              If so, you need a relationship such as:

              JobRequest::_fk_JobID = Job::__pk_JobID

              where __pk_JobID is an auto-entered serial number.

              Then, for each quantity such as the number of bottles, caps etc, you would have a field for this in both tables. In the Job table, you would enter the quantities that are correct for a single unit of product. Looked up value auto-enter field options on the corresponding JobRequest fields would then copy their values from the same fields in the Job table. This makes it possible to modify these settings in the job table without changing the values reported in previous production runs.

              Then you can use calculation fields to compute the total bottles, caps etc for a given production run by multiplying the unit values by the batch size for that production run.


              You have a 6 bottle case, so your unit production for this job is 6 bottles. You enter a 6 in Job::UnitBottles. a matching field, JobRequest::UnitBottles looks up a 6 when the matching JobID number is entered or selected in _fk_JobID. Since this production run will produce 500 cases, you enter 500 into the JobRequest::Batch field. Then a calculation field, cBottles, defined as Batch * JobRequest::UnitBottles will correctly produce the total of 3000 bottles produced by this production run.

              Your "inventory picklist" portal can simply use this script to start a new JobRequest record with the the Job clicked in the portal:

              Set Variable [$JobID ; AllJobs::__pk_JobID ]
              New Record/Request
              Set Field [JobRequest::_fk_JobID ; $JobID]

              The portal would be a relationship such as:

              JobRequest::anyfield X AllJobs::anyfield

              AllJobs would be an added occurrence of Job. Your layout would be based on JobRequest with a portal to AllJobs. A filtering mechanism can be set up on this portal to show only selected subsets of the total jobs if you need that.

              • 4. Re: updating fields

                Phil: I'm a newbee to Filemaker so it will take me a while to digest your answer. I think I understand and it seems to me to be exactly what I'm trying to do. I wish I'd started learning Filemaker 10 years ago! I probably won't get back at this till Monday but thank you for taking the time to even consider my problem. I really thought I had it solved  my problem because the first inputs work fine but when I try to re run a batch of a different batch size, the batch size remains the same. Anyway thanks again. I'll let you know how I get on...just remember I've 10 years catching up to do!