9 Replies Latest reply on Jun 11, 2013 1:26 PM by mperley

    Calculation Field Based on Portal Loop

    mperley

      Title

      Calculation Field Based on Portal Loop

      Post

           My solution has two tables; Orders and Routing Sequence. The relationship is Orders------<Routing Sequence, where each Routing Sequence record has an order number as is foreign key. The Routing Sequence records indicate the different departments each of our orders will eventually go through and contain a sequence number (1, 2, 3, etc.), department name, and status ("c" for complete or null for incomplete).

           I would like to add a new calculated field to the Orders table that indicates the department the order is currently in. Would a loop be the best option here? My thought was that the calculation would loop for the related records according to the sequence number until it got to the first incomplete department and then return that department's name. I am, of course, open to any suggestion that would accomplish the same thing.

           Thank you in advance for the help.

           PS - I cannot change my solution's schema. I am importing/updating records from our company's accounting software.

        • 1. Re: Calculation Field Based on Portal Loop
          philmodjunk

               If you use a two field sort on either your relationship or a portal to Routing Sequence, you can sort first by status, then by sequence and then the first related record will show the department that currently has the order. This can allow you to set up a sorted one row portal to show the current department or if you sort at the relationship level, you can put the field from Routing Sequence directly on your layout to show the current department.

          • 2. Re: Calculation Field Based on Portal Loop
            mperley

                 Hi Phil, as always, thanks for the advice. I see how your suggestion would work when viewing a single orders record. Would this still allow to do some reporting based off of an order's current department? My end goal is to write some automated reports that based on this, and that's why I initially thought that a calculated field would allow for that. Is it your opinion that that's likely not going to work?

            • 3. Re: Calculation Field Based on Portal Loop
              mperley

                   Would it be possible to use the "GetNthRecord" function? I guess the trick there would be to determine which record number coordinated with the current department the order is in. Is that where a loop would come in? Set a variable to count the number of times the calculation has to loop before returning a true value?

              • 4. Re: Calculation Field Based on Portal Loop
                philmodjunk

                     What I describe should be possible for a report. I don't see where form view vs. list view (often the view needed for reports) changes this method.

                     Feel free to describe the report you want to generate from this data.

                     I don't see where GetNthRecord will help here.

                • 5. Re: Calculation Field Based on Portal Loop
                  mperley

                       I think the confusion stems from my limited experience with reports to this point. My intent was to filter the found set of orders first by their current department, and I didn't think that was possible if a field on a record is a portal. I hadn't tried that before, and from your suggestion it must work just fine.

                       In the meantime, I was able to accomplish my goal in a different way. This is the calculation field I have on the orders table:

                        

                       Case ( 
                       GetNthRecord ( ADEPTSEQ::status ; 3 ) = "c"; GetNthRecord ( ADEPTSEQ::deptcode ; 4 );
                       GetNthRecord ( ADEPTSEQ::status ; 2 ) = "c"; GetNthRecord ( ADEPTSEQ::deptcode ; 3 );
                       GetNthRecord ( ADEPTSEQ::status ; 1 ) = "c"; GetNthRecord ( ADEPTSEQ::deptcode ; 2 );
                       GetNthRecord ( ADEPTSEQ::deptcode ; 1
                       ))
                        
                       We at most have 4 departments for an order, but this could be expanded if that changes.
                  • 6. Re: Calculation Field Based on Portal Loop
                    philmodjunk
                         

                              We at most have 4 departments for an order, but this could be expanded if that changes.

                         And that's a potential problem as you have to go into your database and alter it's design to adjust for more departments. What I suggested, which does not need a portal--that's just one of two options described earlier, will automatically adust for the number of departments involved, no design changes needed if your organization gets restructured.

                    • 7. Re: Calculation Field Based on Portal Loop
                      mperley

                           Agreed. When you reference the two field sort in your first response, do you mean you would first sort the department table (in this case, by completion status and then sequence) and then add the related department field to the orders table? Due to previous sort, only one related record would show, and that would be the current department? I appreciate your help with all of this.

                      • 8. Re: Calculation Field Based on Portal Loop
                        philmodjunk

                             Yes, that's the idea.

                             You can specify the sort order at the relationship level and not need portals. Or you can specify the sort order in a portal an use a one row portal. The borders of the portal can be made invisible to so that what you have on your layout looks like a regular field.

                             Either method has advantages/disadvantages and either can be made to work for your reports.

                        • 9. Re: Calculation Field Based on Portal Loop
                          mperley

                               Phil, I finally got what you were saying originally when referring to the sort order of the related table. I did not catch until until recently that the sorting was to be done at the relationship level. I had been sorting the records on a layout based on the order status table, then switching to the orders table and expecting to see something different. I realize now that doing so was pretty laughable.

                               As always, thank you for your help.