4 Replies Latest reply on Jun 23, 2011 11:40 AM by philmodjunk

    Relationship help

    LanceB

      Title

      Relationship help

      Post

      I'm a newbie with FileMaker Pro, but I've been able to figure out most of what I needed. Before I go any further, let me briefly explain what I have

      4 tables:
      Clients
      Invoices
      Projects
      Quotes

      My goal in all this is somwhat simple. I have all my clients in the Client table. Next, I keep all my projects in the Projects. Each project that has been invoiced has been marked as such. Still simiple. When it comes to the invoices, it gets tricky for me. I have a relationship set up to allow me to link my contact info for mailing addresses and any discounts as well as a relationship for the projects. I have a popup menu on my invoices to select a project, which in turn brings in the project name.

      What I WANT: in the popup menu on the invoice for the project, I would like to only see the uninvoiced projects for the client already selected. If it woudn't be too much, I would love to have a script that would mark the included projects as invoiced, which in turn will remove them from the list.

      Help? Is this too much to ask?

      I attached a file showing the project menu with description if that helps get an idea of what I'm talking about.

      Screen_shot_2011-06-20_at_11.46.50_PM.png

        • 1. Re: Relationship help
          Abhaya

          Hi Lance,

           I got your problem.So i am giving some solution for this.

          No doubt you have a Invoice layout and user must invoiced each project.

          so keep a flag field(in invoice table ) and set it to "0" first and while invoice set it to 1.

          keep another calculation field e.g con1 and value =1.

          Then make  a self relationship among the invoice table (ID=ID and con1 not equal to flag) 

          this will bring all the record that are not invoiced.

          Try it and hope it'll bring your result.

          • 2. Re: Relationship help
            philmodjunk

            For starters, what you describe is a conditional value list. See if the following links will help you produce a list of projects for a selected client. Once you have that working, we can take a shot at further limiting the list to just the uninvoiced projects.

            Forum Tutorial: Custom Value List?

            Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

            Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

            • 3. Re: Relationship help
              LanceB

              Well, that took a bit for me to wrap my head around. I finally got it to only populate the project drop down list with projects that are related to the preselected client. Thanks! Would it be doable to only include the projects that haven't been marked as invoiced? If it's a ton of work, I'll just manage with what I have. Honestly, I'm thrilled to have what I have so far!

              • 4. Re: Relationship help
                philmodjunk

                A) Take a look at "option 1" in the forum tutorial.

                If you set up a calculaiton that is blank except for non invoiced projects, your conditional value list can refer to that field to only list non-invoiced projects.

                Or

                B) The other option is to add a calculation field that returns the same value as the data you enter to mark a project as "invoiced" in all records. Then include this field and your project status field with the inequality operator as a second pair of fields in the relationship used for your conditional value lists.