12 Replies Latest reply on Oct 11, 2016 7:17 AM by ChristofferRexen_1

    Relationship based on Unstored Calc. Field

    ChristofferRexen_1

      Hello everyone

       

      So, i'm having a bit of trouble cracking this nut.

      FM14 Advanced

       

       

      I have two tables "Company" & "Policy InPower", related thus Company--<Policy InPower.

       

      Company has a calculated field called _kcld_isToday__lcd which holds "Host Current Time Stamp"(Unstored ofc.)

       

      Now from the Company table, I would like to create a Portal which shows all records from "Policy InPower" based on the following relationship criteria:

       

      - Company::_kp_CompanyID = Policy InPower::_kf_CompanyID

      - Company::_kcld_isToday ≥ Policy InPower::DateBegin (The Policy Start Date)

      - Company::_kcld_isToday ≤ Policy InPower::DateEnd (The Policy End Date)

       

      Now, it works!

      - But I can't search in the newly created Portal "Policy InPower" cause the relationship is based on an Unstored field.

       

      So, how do I bypass this problem, so I can search the portal and still retain the Unstored/Selfupdating field _kcld_isToday__lcd ("Host Current Time Stamp")

       

      Any help would be much appreciated!

      Regards!

        • 1. Re: Relationship based on Unstored Calc. Field
          philmodjunk

          What do you mean by that?

           

          what kind of "search" are you trying to do?

          • 2. Re: Relationship based on Unstored Calc. Field
            ChristofferRexen_1

            Hi Phil

             

            Just any kind of search really, independent of the input search criteria, in any of the fields in the portal.

             

            Although I get this message when I try perform a search, which is caused by the Unstored Field, which the relation is based on.

             

            "This operation cannot be performed because one or more of the relationships between these tables are invalid."

             

            Is it possible?

            • 3. Re: Relationship based on Unstored Calc. Field
              wimdecorte

              You don't need an unstored calc and a relationship to show what policies are in effect.  Since the date does not change during the day I would run a server-side schedule at night (past midnight) to update the policies to set a flag field to 'true' or 'false' if the policiy is in effect.  Then you can search on that flag field and relate to it to your heart's content because all the relevant bits are stored.

              • 4. Re: Relationship based on Unstored Calc. Field
                ChristofferRexen_1

                Hi Wim

                 

                I am familiar with that solution.

                But I have a manager, who want's the relationship handled by Date Fields. (Red Lights!)

                 

                So... Is it possible?

                • 5. Re: Relationship based on Unstored Calc. Field
                  wimdecorte

                  ChristofferRexen_1 wrote:

                   

                  But I have a manager, who want's the relationship handled by Date Fields. (Red Lights!)

                   

                   

                  Sorry, that does NOT make sense.  I would certainly put my foot down here.  Consider your position carefully here...

                   

                  ChristofferRexen_1 wrote:


                  So... Is it possible?

                   

                  A search like you are after is do-able with a script, just take the user's input, go to the policies table in your script, do the search there then use a GTRR from Policies to Companies on company ID to get the resulting found set of companies.

                   

                  It is a little more work, but the workflow can be really elegant and you'll have a lot more control.

                  • 6. Re: Relationship based on Unstored Calc. Field
                    ChristofferRexen_1

                    Hi Wim

                     

                    The search I am after, should not require a script.

                     

                    It should just be based on a couple of fields, an Unstored Calculated Date field (Today) with two fields from Policy.

                     

                    Today ≥ PolicyBeginDate

                    Today ≤ PolicyDateEnd

                     

                    So our clients always have a visual of the active policies.

                     

                    Is that possible, based on the Unstored Calc Date field?

                    • 7. Re: Relationship based on Unstored Calc. Field
                      philmodjunk

                      Here's why I asked "what kind of search"?

                       

                      When you have fields from a related table--such as from a portal, you need to be clear as to whether you are searching for records in the layout's table occurrence or the portal's.

                       

                      Are you trying to find Company records with a specific related Policy or are you trying to find specific Policy records for the current company?

                       

                      You may need to search on a Policy in Power layout to find your policy records and then do a Go To Related records, match current found set to pop up the corresponding set of company records. Your search criteria can include the appropriate date criteria to reproduce the logic of your relationship matching.

                       

                      If you get a large set of Policy in Power records, there can be a significant delay during the GTRR, however.

                      • 8. Re: Relationship based on Unstored Calc. Field
                        ChristofferRexen_1

                        I see

                         

                        I am trying to find every related Policy InPower, which is related to the "Company" table.

                         

                        Let's say, Company X, has 6 related policies.

                        Only 3 of them are In Power, based on the two fields from the "Policy" table;  DateBegin and DateEnd.

                         

                        Policy A - InPower - 01.01.2016 - 31.12.2016

                        Policy B - InPower - 01.01.2016 - 31.12.2016

                        Policy C - InPower - 01.06.2016 - 31.12.2016

                         

                        Now from the Company table, where I put my "Policy InPower" portal, I would by a relation like to find/search for all the above Policies In Power, based on the above search criteria (3 policies) - where the other (3 policies) are left out of the portal / find/search criteria, based on the relationship below, like this:

                         

                        - Company::_kp_CompanyID = Policy InPower::_kf_CompanyID

                        - Company::_kcld_isToday ≥ Policy InPower::DateBegin (The Policy Start Date)

                        - Company::_kcld_isToday ≤ Policy InPower::DateEnd (The Policy End Date)

                         

                        Although, as stated before above, _kcld_isToday is an Unstored Calc Date field, which means that the clients can't search in the table, but instead of met by this message:

                         

                        "This operation cannot be performed because one or more of the relationships between these tables are invalid."

                         

                        The search in the portals, in any of the given fields put into the portal, is possible if I make the _kcld_isToday a stored calculation.

                         

                        So, is it possible to bypass this, still maintaining the relationship and making an unstored calc, etc.

                        Regards

                        • 9. Re: Relationship based on Unstored Calc. Field
                          wimdecorte

                          Some things are not clear to me:

                           

                          - does the portal as it is now show only the "in power" policies?  Sounds like it, just based on the relationship you have set up.

                          - if so, why would the users need to search in the portal to show them (perhaps I don't understand your write-up)

                          1 of 1 people found this helpful
                          • 10. Re: Relationship based on Unstored Calc. Field
                            ChristofferRexen_1

                            - does the portal as it is now show only the "in power" policies? Sounds like it, just based on the relationship you have set up.

                            Yes that is correct, only the "In Power" policies.

                             

                            - if so, why would the users need to search in the portal to show them (perhaps I don't understand your write-up)

                            I can't tell you why the user would need to search in the portal.

                            I just know that when everything comes down, the clients, including my manager, would like the clients to be able to search in the given portal.

                             

                            Let's say, they make a search to lookup how many customers who made a Motor insurance.

                             

                            Does it make sense?

                            • 11. Re: Relationship based on Unstored Calc. Field
                              wimdecorte

                              As I said earlier; I would construct a scripted search for them.  You said before "a script should not be necessary" and I disagree.  You'll have a lot more control if you script it.  And from a UI point of view you can make that just as intuitive as them searching in a portal.

                              1 of 1 people found this helpful
                              • 12. Re: Relationship based on Unstored Calc. Field
                                ChristofferRexen_1

                                Let's try with a script then.

                                 

                                Although, how would you construct this?

                                I don't quite follow...