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?
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
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
That´s not so good
I hope this
explanation will help You in helping me
These appear to be the relationships involved:
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:
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.