11 Replies Latest reply on Oct 19, 2012 8:38 AM by philmodjunk

    Portal does not show anything

    Emmanuel

      Title

      Portal does not show anything

      Post

           I understand how portals work. Yet, I am unable to get that one working, it does not show anything like if there would be no link at all).

           I started from FM12 Invoices starter solution and added a table called "fiscal year". In that one, I have "year match field" which are about 15 records: "2001-2002", "2002-2003" ... "2011-2012" in text format. It is linked to invoice table through "fiscal year" field which is calculated (text result) as followed:

           Case (

           Fiscalyear start date < "Invoice Date" < Fiscalyear end date; "2001-2002";

           Fiscalyear start date < "Invoice Date" < Fiscalyear end date; "2002-2003"; .....)

            

           That part works since I do have the results. However, it does not want to connect to "fiscal year" table. What is wrong ??? It must be some details that are I don't get  

        • 1. Re: Portal does not show anything
          philmodjunk

               As posted, this calculation:

               Case (

               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 )
                       ] ;
                       Case (

               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 ) )

          • 2. Re: Portal does not show anything
            Emmanuel

                 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!

                  

                 Thank you

                  

            • 3. Re: Portal does not show anything
              philmodjunk

                   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.

              • 4. Re: Portal does not show anything
                Sorbsbuster

                     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?

                • 5. Re: Portal does not show anything
                  Emmanuel

                       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? 

                  • 6. Re: Portal does not show anything
                    Sorbsbuster

                         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'?

                    • 7. Re: Portal does not show anything
                      philmodjunk

                           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.

                      • 8. Re: Portal does not show anything
                        Emmanuel

                             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";
                              
                             ) )
                        • 9. Re: Portal does not show anything
                          philmodjunk

                               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.

                          • 10. Re: Portal does not show anything
                            Emmanuel

                                 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?

                            • 11. Re: Portal does not show anything
                              philmodjunk

                                   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.