10 Replies Latest reply on Mar 29, 2013 12:43 PM by philmodjunk

    Select Records from Portal to Add to Portal

    YukioKina

      Title

      Select Records from Portal to Add to Portal

      Post

           Hey folks.

           Thanks to PhilModJunk for assisting with my last inquiry.  It really helped.

           I've been working more in FMP12 and have gotten pretty far into what I need to do, but have come to another road block that's stumped me for the better part of a couple weeks now.  All my searching has so far yielded no results.

           I have a pretty complicated relationship going between several tables and TOs shown below.  What I am trying to do is place a portal in Weddings to show selected records from Related Services - a TO of Services.  That I have done no problem.  But my issue is wanting to have check boxes in that portal which will then allow me to select certain services from the portal to add as new records to the Lines table, which puts them into a portal on my Invoices table.

           The issue I'm having is: I don't know what fields I need to add to which tables for the checkbox to say "take this record from Related Services and add it as a record to Lines for this particular record in Weddings".

           As much as I've tried to self-analyze the situation and search around other forums and websites for solutions, I am still coming up empty.  Thanks to anyone who might be able to lend a hand.

      relat.jpg

        • 1. Re: Select Records from Portal to Add to Portal
          philmodjunk

               First the basic script. To get a "checkbox" like behavior for the script, it needs to check and see if the particular service has already been selected for teh current record in weddings. If so, it deletes that record from Lines. If not, it adds it.

               Freeze window
               Set variable [$ServiceID ; value: RelatedServices::_kp_services_id ]
               Set Variable[$WedID ; value: Weddings::_kp_Wedding_id ]
               If [ IsEmpty ( Filtervalues ( List ( Lines::_kf_services_id ) ; $ServiceID ) ) // service has not been selected ]
                  #Add selected service
                  Go To Layout [ "Lines" (Lines) ]
                  New record/Request
                  Set Field [Lines::_fk_Wedding_id ; $WedID ]
                  Set Field [Lines::_fk_Service_id ; $ServiceID ]
               Else
                  #Remove selected service
                  Go To Related Record [Show only related records; From table: lines; Using layout: "lines" (lines) ]
                  Enter Find Mode []
                  Set Field [Lines::_kf_Service_id ; $ServiceID ]
                  Constrain Found set []
                  Delete Record [No dialog ]
               End If
               Go To Layout [original layout]

               Now to show the "check box" in the portal row so that it changes when clicked.

               IsEmpty ( Filtervalues ( List ( Lines::_kf_services_id ) ; RelatedServices::_kp_services_id ) )

               can be used in a conditonal format expression to make layout text such as an X or a tickmark to disappear.

          • 2. Re: Select Records from Portal to Add to Portal
            YukioKina

                 Brilliant!  Thank you so much!  That worked exactly how I needed it to!

                 If I may, I'd like to add one twist to it: is there an addition I can make to a duplicate script so that the text boxes act like radio buttons?  To have a second portal of services but want to allow only one to be chosen.

            • 3. Re: Select Records from Portal to Add to Portal
              philmodjunk

                   That becomes this script:

                      Set Field [Lines::_fk_Service_id ; RelatedServices::_kp_services_id ]

                   Just make sure that "allow creation of records via this relationship" is enabled for Lines in the relationship to it.

              • 4. Re: Select Records from Portal to Add to Portal
                YukioKina

                     Again, thanks for the solution.

                     The radio effect works great, although there's a problem - if I select some of the services from the "checkbox" portal and THEN go and choose a service from the "radio" portal, it will delete one of the added "checkbox" services in what seems to be a random manner.  I can't seem to figure out why it deletes one of the services, or how it chooses which one it's deleting.

                • 5. Re: Select Records from Portal to Add to Portal
                  sparrow_design

                       Hello,

                       It seems like the RADIO Script is simply overwriting the FIRST VALUE (per relationship - may be sorted?) among all the values that may exist at that point - form checkbox entries. Either ADDING a new Field value (if Radio button was empty befoe clicking), or CLEARING the Field value.

                       So..you may need to add the following to your RADIO BUTTON script:

                  If [ count ( lines::_ID_pk ) > 1 ] //more than 1 record in Lines table - shown in portal

                  Set Variable [ $_id  ;Value = RelatedServices::_kp_services_id ]

                  Freeze Window

                  Go To Related Record [Show only related records; From table: lines; Using layout: "lines" (Lines) ]

                     Delete RecordAll Records [No dialog ]

                  Go to Layout [Original Layout]

                  Set Field [Lines::_fk_Service_id ; $_id ]

                  End If

                        

                  Exit Script[]
                        
                  Hope this helps,
                        
                  -David
                  • 6. Re: Select Records from Portal to Add to Portal
                    philmodjunk

                         You wouldn't want to use the same underlying table for a radio button type format and also for a check box format. You'd need to use separate Join tables for each.

                         Can you explain in more detail what you are trying to do?

                         It may be that the set field step for the "radio button" version should set a value in a field in the parent record instead of the join table record.

                         Set Field [Weddings::_fk_Service_id ; RelatedServices::_kp_services_id ]

                    • 7. Re: Select Records from Portal to Add to Portal
                      YukioKina

                           David - thanks for the assist, but I tried your script and it does the same thing.

                           Phil - under Weddings I have three portals; two of them with service packages and one of them with individual services.  These services come from related table Related Services, a TO of Services.  And in my image above, you can see I have it set that when they choose an item from the portals in Weddings, it adds that item as a record to Lines which then allows me to pull those records into a portal in Invoices.

                           I'm trying to make it so the two portals with packages can only have one item selected between both portals, and then add as many items from the individual services portal.  Which your script allowed me to do, but as I mentioned, if I select individual services first, then add a package, it will delete a line from the individual portal.  Clients will often know what individual services they want but not choose a package till later.

                      • 8. Re: Select Records from Portal to Add to Portal
                        philmodjunk

                             How do you distinguish the individual items from your "package" items?

                             If you select a package do you want all existing individual items retained or all deleted? (Both options are possible and are not mutually exclusive.)

                        • 9. Re: Select Records from Portal to Add to Portal
                          YukioKina

                               In services, every item has two item IDs, one serial number auto-generated by FMP (SVC0000000001) and an internal item number we create for each service.  The package items will have an identifier such as "P-" or "T-" somewhere in the item number.  Each item also has a category checkbox for different event types (for later when we add other events to the database).  So for example, the portal with the individual services is filtered to look for any items that are in category "Wedding" and does not contain "P-" in the item number.  In the two package portals, they both look for services which only have the "P-" in the item number for the first portal and "T-" for the second.

                               If a package is selected, I want all individual items to remain, whether the individual items were selected before or after the package was chosen.  This includes if the package is changed later.

                          • 10. Re: Select Records from Portal to Add to Portal
                            philmodjunk
                                 

                                      If a package is selected, I want all individual items to remain, whether the individual items were selected before or after the package was chosen.  This includes if the package is changed later.

                                 Then I gathre that the main concern is that for a given Wedding record, only one itme with "P" and only one item with "T" may be selected.

                                 I think you'll need a relationship, rather than a portal that matches only to "P" and another that matches only to "T" lineitems.

                                 You can define a calculation field in lineitems that replicates your filter logic but returhs a specific value for individual, P and T items. This can be an auto-enter calculation if it needs to reference data in other tables in order to be an indexed value.

                                 Let's say you set up LIneItems::Type as that calculation to return "T", "P" or "I"

                                 Then you can set up a relationship to a new occurrence of lineitems with this relationship:

                                 Weddings::_kp_weddings_id = LInes|P::_kf_Weddings_id AND
                                 Weddings::constP = Lines|P::Type

                                 Enable "allow creation" for Lines|P and then Set Field [Lines|P::_kf_Services_id ; RelatedServices::_kp_Services_id]

                                 will work as our one line script.

                                 ConstP would be a calculation field defined to return the text "P".