3 Replies Latest reply on Mar 29, 2012 12:14 PM by philmodjunk

    Value list sometimes without any posts ????

    TafeLoursen

      Title

      Value list sometimes without any posts ????

      Post

      I hav 2 tables - "Projekts" and "Invoices" related to each other through the field "ProjektID".I also have an inner join betweenTOs "Projekts 1" and "Projekts 2" throgh the fields "ProjektStatus 1" and "ProjectStatus 2" for searching for "Ongoing projects" which are the only ones I want to send an invoice to.The field "ProjectStatus 1" is always set to be "Ongoing project"

      I have made a "Value list" for being used on a layout based on the "Invoice"-fields. The "Value list" consists of "ProjectID"  from "Projects 1"and a second field from the same table.

      If I have made an invoice in any way and want to do onother one my value list will show all "Ongoing projects" as I want.

      If I shall make my first invoice ever the value list doesn´t show any "ongoing procets" at all - it´s totally empty.

       

      What´s wrong and how shall I do to get a "Value list" always showing "Ongoing projects"

       

      Very greatful for any help.

      I have tried for about  3 days now without succeding

      Tafe Loursen

        • 1. Re: Value list sometimes without any posts ????
          philmodjunk

          Can you provide more details on how you've set up your relationships?

          What fields are used to match records in each relationship?

          Exactly how have your set up your value list? What values is it supposed to list?

          • 2. Re: Value list sometimes without any posts ????
            TafeLoursen

            Thank You for Your answer.

            Here are som comments I hope will give You a better insight how I have done !!!

            My valuelist has 2 own values written by myself

            -       
            Ongoing project

            -         
            Project finished

             

            My inner
            join is intended to show ”Ongoing projects”  in the fields “”ProjectStatus 1” and in “ProjectStatus2”which
            works well on other layouts but when I build a layout based on the fields from “Invoices”
            the value list works well if there is an
            invoice produced for one of the “Ongoing projects” but don´t work because it will
            be empty if there is no invoice made yet.

            That means I can´t write my first invoice to an
            “Ongoing project”.

            That´s not so good

             

            I hope this
            explanation will help You in helping me

             

            Best
            regards

            • 3. Re: Value list sometimes without any posts ????
              philmodjunk

              These appear to be the relationships involved:

              Projects 2>----<Projects----<Invoices

              Projects::Project Status 2 = Projects 2::Project Status 2
              Projects::ProjectID = Invoices::ProjectID

              As I understand it, you want to be able to create a new record on the Invoices layout and then be able to choose from a value list listing those projects that have "Ongoing Projects" in the Project Status 2 field in order to link the new invoice record to the correct project.

              You can't use the "self join" relationship between Projects and Projects 2 for this because you must first be able have a valid link to a Project Record before you can access related records in Projects 2 and that's the very thing you are trying to do here in the first place.

              Both are described in this tutorial on conditional value lists: Forum Tutorial: Custom Value List?

              Option 1 is the simplest approach, you can define a calculation field in Projects as: If ( Project Status 2 = "Ongoing Projects" ; ProjectID ) and use it as the source of values for your value list.

              It's also possible to define an additional relationship between Invoices and Projects that only matches to ongoing projects if you define a calculation field, constOngoing, as:

              "Ongoing Projects"

              Select "text" as the return type for this calculation field and link it to Project Status 2 in a new occurrence of Projects and then you can use this new occurrence of projects as the source of ProjectID values and you'd specify Invoices as the "starting from" table occurrence.