As posted, this calculation:
Fiscalyear start date < "Invoice Date" < Fiscalyear end date; "2001-2002";
Fiscalyear start date < "Invoice Date" < Fiscalyear end date; "2002-2003"; .....)
Does not work.
It should read something like:
Let ( [ d1 = day ( Fiscalyear start date ) ;
d2 = day ( Fiscalyear end date );
m1 = month ( Fiscalyear start date ) ;
m2 = month ( Fiscalyear end date )
Date ( d1 ; m1 ; 2001 ) < Invoice Date and Invoice Date < date ( d2 ; m2 ; 2002 ) ;"2001-2002";
Date ( d1 ; m1 ; 2002 ) < Invoice Date and Invoice Date < date ( d2 ; m2 ; 2003 ); "2002-2003"; .....) )
That will generate the needed value to use in your match field, but it's a calculation that requires adding a new term to the case function with each new year.
This calculation eliminates the need to do that:
Let ( y = Year ( Invoice Date ) ; If ( Month ( Invoice Date ) < Month ( Fiscalyear start date ) ; y-1 & "-" & y ; y & "-" & y + 1 ) )
Your part works perfectly... however it still does not fix my issue :(
So we got our "invoice" table "fiscal year" field set up with entry such as "2001-2002" in text format.
I create this new table: "fiscal year" that has a field "fiscal year match field" and has 13 entries in text format: "2001-2002"; "2002-2003"; ... with a portal based on "invoice" table. I connected:
Fiscalyear::fiscalyearmatchfield >>>>> Invoice::Fiscalyear
It just does not relates!!! I even imported invoice::fiscalyear into fiscalyear::fiscalyearmatchfield to make sure there is no typo error but it did not change anything. I really don't get it!
Both fields should be of the same data type. In this case, the calculation field should have text specified as the return type to match to a field of type Text in the Fiscalyear table.
And since these are text fields, the data must perfectly match character for character. the presence or absence of a single space character, for example, will keep values from matching even though they look identical.
I assume the listing of FiscalYearToMatch data looks to you exactly like the FicalYear listing in the Invoice Table.
On the FiscalYear Table, put a field beside the FiscalYearMatchField. Make it the related Fiscal Year from the invoice table. So it would be:
FiscalYear To Invoice Table By FiscalYearToMatch Field :: Fiscal Year
How does the listing look?
Thank you for you anwsers!!
I have been doing some investigation. I did a match test in the invoice table with an if function to see if both fields were alike, and they are.
Then, I tried the test suggested by Sorbsbuster, the field gives me " missing index" message. By reading of some forums I have learned that a relationship cannont be established if the related fields are not indexed. But I can't indexed the field from invoice because it is
"The calculation “Fiscalyear” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage"
What is there to do?
The help comment is very helpful in this case. There is some component of the foreign key calulation that "...references a related field, a summary field, an unstored calculation field, or a field with global storage". Can you post the full calculation you are now using for that?
If you go to the field in Manage Database can you turn on its storage option to be 'Indexed'?
You'll need to change the design of your system so that the field can be indexed.
Presumably, your fiscal year start and end date fields are unstored calculations. This need not be the case.
I did try to index the field but it gives me this message that I cannot. tThe field calculation are the following:Let ( [ d1 = Day ( 1 ) ;d2 = Day ( 31 );m1 = Month ( 8 ) ;m2 = Month ( 7 ) ] ;Case (Date ( m1 ; d1 ; 2001 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2002 ) ;"2001-2002";Date ( m1 ; d1 ; 2002 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2003 ); "2002-2003";Date ( m1 ; d1 ; 2003 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2004 ); "2003-2004";Date ( m1 ; d1 ; 2004 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2005 ); "2004-2005";Date ( m1 ; d1 ; 2005 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2006 ); "2005-2006";Date ( m1 ; d1 ; 2006 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2007 ); "2006-2007";Date ( m1 ; d1 ; 2007 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2008 ); "2007-2008";Date ( m1 ; d1 ; 2008 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2009 ); "2008-2009";Date ( m1 ; d1 ; 2009 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2010 ); "2009-2010";Date ( m1 ; d1 ; 2010 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2011 ); "2010-2011";Date ( m1 ; d1 ; 2011 ) ≤ DateMaxProjet and DateMaxProjet ≤ Date ( m2 ; d2 ; 2012 ); "2011-2012";) )
This is not the calculation that you posted originally, that referred to an invoice date. Why does it refer to DateMaxProjet? I would guess that this field is one that cannot be stored--as it probably sums related records or is a summary field--but in the context of this issue, it does not make any sense to me that it would refer to such a field instead of an invoice date field.
Ok, you got me: I lied!!! I wanted to go fast in the understanding of the issue but it seems it is catching me up!!!
I started from the invoice starter solution to adapt it to R&D for some product formulation. So the parrallel is such that
Products >>> Invoice Data >>> Invoice >>> Customer
is modified to
Ingredients >>> Formulation data>>> Formula >>> Project (and now >>> fiscal year)
So actually, a fiscal year has many project, a project many formulas tryouts that are made of many ingredients. Now, because the formulas with a same project may be scattered through a large timeframe, I created a field project min date and project max date which gets the first and the latest formulation that was made within that project in particular. When a project is done, we can ask for R&D credit. This is why I want to know in what fiscal year the projet falls in.
You have an idea of how I could redesign this thing?
You need to not use a summary field or an unstored calculation (don't know which method you used to get the max and min dates).
I suggest you find a way to use a script to capture the min and max dates and enter them into simple date fields which can then be indexed, stored values that will work as a match field on the "many" (portal) side of the relationship.
As an example, each time you commit a record, the OnCommitRecord Trigger could perform a script that captures the min and max values and stores them in a field.
I strongly recommend that you get rid of the case function here to as it requires continual editing as new date ranges are entered. I showed you another way to get the needed value that does not require such a case function.