How can I setup OR in edit relationship? I have values set in global table. Thank you in advance.
While there are ways to achive this, it's not as easy as simply changing a setting to "OR" as all of the predicates in the relationship are strictly AND.
In this case with your predicates being different, the way in which I would do this is with the help of a couple of additional relationships. Consider splitting each predicate into its own relationship e.g.:
Start Date ≤ ETA
and a separate relationship where
Status ≠ Status
With that done, you can go back and modify your original relationship above to something like this:
ID_Calc = ID
This is assuming that "ID" in this case is yoru primary key field that resides in your MAIN_Main table.
ID_Calc is a new calculation field that we create. The result of this calculation is to pull through record ID's from both of the 2 new earlier relationships created, e.g.:
List ( daterelationship::ID ) ;
List ( statusrelationship::ID )
This pulls through all primary key IDs who meet EITHER of the two conditions (effectively an OR).
With this final list of ID's, we use this in the main relationship which has been modified to relate from this return delimited list of ID's through to the primary key.
So what we have actually done is to say "Find me all records whose ID is either from the list of IDs which match on the date, OR from the list of IDs who match on the status).
It's a bit of a roundabout way to do an OR in a relationship. There are a couple of other ways you could go about this, but the fact your predicates are ≤ and ≠ make it a little more difficult to achieve those ones, and performance can become a consideration.
Note that the above technique does pull through the ID's from records, meaning that record data is downloaded prior to the relationship being evaluated. If you are relating through to many thousands of records, this is not going to be an optimal performer, but if you are dealing with less records (or over local connection) it should perform fine.
(another way you could achieve the same effect is to obtain your ID's using an ExecuteSQL query, and then use that as the basis of your calculation for the IDs_Calc field).
Thank you so much! I will try to understand the logic and give it a try.
"another way" may be easier to implement.
ExecuteSQL ( "
" ; "" ; "" ;
If you use this in global calculation field (forced to be stored), will update only when Globals fields are changed.
Thank you. This works. Coming from SQL Server, this is easier for me to understand and implement. However, I also understand weebicks logic. Thank you all for the help.
There are plenty of SQL DBAs here that can help 'translate' whatever you need into FileMaker-speak.
Retrieving data ...