8 Replies Latest reply on Sep 28, 2010 12:06 PM by BrianWright

    Edit a Calculated Field and Change the Source Field

    BrianWright

      Title

      Edit a Calculated Field and Change the Source Field

      Post

      We have a daily production meeting to go over where our copy editors and visual artists are with their current projects.  Right now, we have a report layout that is working very well for us, but it uses several calculated fields and right now we can't update records from the new layout.

      We have a field "report status" that pulls data from a "status" field if we are still working on the project but then pulls data from a "vendor" field when the job goes into production.

      Is there any way to keep the calculation in place, but be able to edit and have changes go back to the source field?

      Or, is it possible to have a layout display a certain field based on a calculation, ie: If the job is in production, show the vendor in column 3, otherwise show the status?

        • 1. Re: Edit a Calculated Field and Change the Source Field
          philmodjunk

          Is there any way to keep the calculation in place, but be able to edit and have changes go back to the source field?
          Yes, you have several options here. The simplest, based on what you describe is to make the source fields accessible for editing. You can place a button next to the calculation field that pops up a custom dialog that links an input field to the source field, you can open a new window with a layout that contains the source field, or the button click can just switch the user to an edit layout. You could also just place the source field on the same layout as your calculation field.

          There might also be a way to use a text field with an auto-entered calculation for this, which might enable you to just click in the field and type in a different value, but I'd need to know more about how you need to use this field and how you've structured your database before I could tell if this would work for you.

          Or, is it possible to have a layout display a certain field based on a calculation, ie: If the job is in production, show the vendor in column 3, otherwise show the status?
          That is also possible. If ( JobStatus = "Production" ; Vendor ; JobStatus ) is one such calculation. A Case function can be used in case of IF if you might have more than two possible outcomes.

          • 2. Re: Edit a Calculated Field and Change the Source Field
            BrianWright

            Thanks for your help!  Explaining our database is a little tricky, so bear with me here.

            I set up several calculated fields like this to get our report layout to work.  We use a list report with grouped data to break up our current jobs.  Our first grouping uses a calculated group "Report Group" with the following calculation:

            If(status="In Production";status;If(Project Status="On Hold";"On Hold";If(Project Status="Tracking";"Tracking";Active Employee)))

            This allows us to first show jobs that are currently being worked on with a breakdown by employee, then show all of our jobs currently in production, then our jobs on hold and finally any jobs we are tracking.

            We have a main layout "Publications" where artist and editors enter information about projects they're working on.  The artists and editors are responsible for entering a "Status" (designing, need feedback, revisions, etc)  which is selected from a drop-down menu.  When a job enters production, the Project Status changes to In Production.  If a job is on hold, or being tracked after completion, there is a separate field "Project Status" where they can check the corresponding box.

            For our meeting report, I created a "Report Status" field that performs the following calculation:

            If(Report Group="In Production";vendor_name;status)

            In our report this shows the design status for jobs our employees are working on and the vendor for jobs in production.

            All of the calculated fields I've had to set up have made this report quite a mess and I'd like to get rid of them if possible.  Ideally, I'd like to take my laptop into the meeting, run the script for this report and be able to edit everything from the report layout without having to switch back to the publications layout to make changes.

            I hope this helps you understand my problem, or maybe I don't fully understand it either!

            • 3. Re: Edit a Calculated Field and Change the Source Field
              philmodjunk

              If(status="In Production";status;If(Project Status="On Hold";"On Hold";If(Project Status="Tracking";"Tracking";Active Employee)))

              Try it this way for a simpler expression but same results:
              Case ( status = "In Production" ; status ;
                         Project Status = "On Hold" or Project Status = "Tracking" ; Project Status ;
                         Active Employee )

              What I don't follow is why you need two fields for this. Couldn't you use one field with the values: "In Production", "On Hold", "Tracking" , and whatever value is entered, if any for the active employee result shown above.

              In either case, all you need to do is put the Status and/or Project Status fields on your report layout with drop downs or pop up menu formats so that you can edit them. You can locate the field to the right of the right margin or set the field to "Hide When Printing" in the inspector so that these fields don't show when you print the report.

               

              • 4. Re: Edit a Calculated Field and Change the Source Field
                BrianWright

                I would love to only use one field for this.  Unfortunately, management wants a duplicate of an old, Excel based report.  I started a few weeks ago and already know more about FileMaker than everyone else in the office, and they've been using it for over 2 years now!  I would like to scrap our current database and do a clean sheet redesign, but change is discouraged in my workplace.  There was grumbling because some of the field fill colors didn't match between FileMaker and Excel!

                I guess I should change my question.  Rather than creating calculated fields to use in this report layout, can I create a layout that uses a calculation to choose which field it diaplays for a record?  ie: In the status column, display the vendor field if the job is in production, otherwise display the status field.  I want to be able to update the Status or Vendor Field directly from the report layout and I can't do that with a calculated field

                I hope I'm explaining this right, but I don't think Filemaker has the capabilities I'm looking for.

                • 5. Re: Edit a Calculated Field and Change the Source Field
                  philmodjunk

                  What you describe is exactly the same approach as your current calculation field, but with slightly different fields.

                  If ( status = "in production" ; vendor field ; status field )

                  When it comes to "change is discouraged", I hear you loud and clear. I have the same problem here : (.

                  • 6. Re: Edit a Calculated Field and Change the Source Field
                    BrianWright

                    So how, do I set up the layout to have it calculate which field to pull?

                    • 7. Re: Edit a Calculated Field and Change the Source Field
                      philmodjunk

                      The above is an example of a calculation field. Simply define it and add it to your layout.

                      I believe this all comes back to needing to change what is displayed from your report layout. Any way you slice it, you'll need to add something to your layout that you click or type in to initiate the change. Since you are going to have to add this anyway, I'd just add the source field to the report layout so that I can edit it directly which is what I suggested in my original response.

                      Even with the original spreadsheets, you would have had to do this.

                      • 8. Re: Edit a Calculated Field and Change the Source Field
                        BrianWright

                        Ok, I got it figured out!  Thanks for your help, my day at the office is now a little easier.