1 2 Previous Next 17 Replies Latest reply on Jul 29, 2014 7:26 AM by Willz

    Script copies content from data entry portal field

    Willz

      Title

      Script copies content from data entry portal field

      Post

           Need script that copies content from data entry portal field of a matching row

           table 1 (contact info) this is the portal table (data entry portal).
           table 2 (info separated) this is table to paste copied data into.

           tabel 1 (contact info portal) has two fields "Type" and "info" (data entry).

           "Type" field has selected content - one of dropdown list.

           - phone
           - email
           - fax
           - url

           - need to find row of portal that has "type" content = "phone".
           then copy data form the other field "info" in the row.  

           - paste the data into table 2 field


           find portal row where field "type" = Phone
           copy data from row field called "info"
           paste into table 2

           loop
            

        • 1. Re: Script copies content from data entry portal field
          nihmbrisby

               Summary: temporarily sort the relationship and replace field contents with calculated result- table1:info

               Pretty sure this will work (I just tried it and it worked (without the sorting part though as I don't want to fool with my graph at the moment)), but please back up before you try this as it edits multiple fields.

               Go into your relationship graph.  Choose the relationship between the parent table (Table 2) and the child table (Table 1).  You are going to temporarily sort this relationship so that the record of type 'phone' is the first.  If 'phone' is alphabetically last, then sort reverse alphabetical otherwise sort by custom order.  

               Click show all (so that your looking at all records, not a found set).  Put your cursor in the 'table 2 field.' Choose "replace Field contents..." in the records menu.  Choose the third option ("replace with calculated result"). Click specify and choose table 2:info.  Click Ok, and then in the replace field contents menu click "replace."

                

          • 2. Re: Script copies content from data entry portal field
            nihmbrisby

                 Actually what may be easier than sorting by a custom value list is just to modify the relationship temporarily so that Primary key = foreign key AND table2::phone (which is just a field equal to 'phone') = table1::type.  Anyways the idea is to be sure that the 'phone' record is the first one filemaker sees for the purpose of fetching related info.

            • 3. Re: Script copies content from data entry portal field
              Willz

                   Nihm,

                   I already did a similar thing - not what I need.

                   My question maybe confusing - Here is question re-stated.

                   I already have all the CURRENT records updated with the data separated from the portal entry. 

                   What I need is a way to get the data for future NEW records that are added.

                   1. PORTAL TABLE
                       has two fields
                   a.contact info
                   b.contact type – (this field has dropdown list of 4 items)
                           - phone
                           - email
                           - fax
                           - mobile

                   2. OTHER TABLE
                       Receives data from 1. PORTAL TABLE copied into 4 fields:
                           - copy of phone number
                           - copy of email address
                           - copy of fax number
                           - copy of mobile number

                   - find row in portal that has "type" content = "phone".
                   - find row in portal that has "type" content = "email".
                   - find row in portal that has "type" content = "fax".
                   - find row in portal that has "type" content = "mobile".

                   Paste the data into 2. OTHER TABLE corresponding field.

                    

                    

              • 4. Re: Script copies content from data entry portal field
                nihmbrisby

                     If the records don't yet exist, the first thing that comes to mind is to define the fields that will 'receive' the data as look ups: (http://www.filemaker.com/help/13/fmp/en/html/relational.11.23.html).  If you need conditional logic to determine the lookup, the next thing that comes to mind is setting the OTHER TABLE fields as calculations driven by set field/set field by name.  If you'd rather these not be calcs, then you could write a script revolving around set field/set field by name which then has either a triggering event or is activated by user input (ie a button).  

                     I wanted to provide solutions first (to the extent I can).  I get annoyed when people answer my questions by simply second guessing the needs I've outlined.  So second- I want to question your needs.  That is- do you really need to duplicate data?  It's not the best sign, though of course you very well may need to do it.  I get the impression you're migrating data?  Anyways, just wanted to say that as I've benefited greatly from others who have prodded me to take a second look at my assumptions.

                • 5. Re: Script copies content from data entry portal field
                  Willz

                       Yes I do need to migrate the data out of the portal and into separate fields .
                       It’s for a QuickBooks migration in order to push this data into QB.
                       Can’t send this contact data from a portal, need separate fields.
                       If a number in the portal (see portal table previous post)  is a fax number then I need to figure a way to
                       pull it out of portal into a separate field called “fax number”, etc.

                       Conditional logic sounds like what I have to figure out.
                       Don’t want to have a script that user has to fire off.
                       The script or lookup activates when data is entered into the data entry portal.

                       not sure how would Relationship be?
                       1. portal table
                       2. other table

                       the lookup condition logic is something like this:
                       - find row in portal that has “type” = “fax” copy content of field called "info"
                       - find row in portal that has “type” = “phone” copy content of field called "info"
                       etc.

                       Thanks for your help!
                        

                  • 6. Re: Script copies content from data entry portal field
                    nihmbrisby

                    "not sure how would Relationship be?
                         1. portal table
                         2. other table"

                    I was assuming the 2 tables were already related- Other table being the parent, portal table being the child (ie Other table's primary key = portal table's foreign key.  Is that not correct?  I mean, it must be if you're displaying this info in a portal right?

                    "the lookup condition logic is something like this:
                         - find row in portal that has “type” = “fax” copy content of field called "info"
                         - find row in portal that has “type” = “phone” copy content of field called "info""

                         So assuming I misunderstood you and the tables are related, I'm also going to assume that the portal table only has one each of the four types for every contact (that's the impression I've gotten so far).

                         A script will do the work here.  Since you've ruled out user input, you have to decide when and where you want the script to trigger.  Here are your options: http://help.filemaker.com/app/answers/detail/a_id/7465/~/understanding-and-using-script-triggers

                         My personal preference is for using relationships, not finds and go-to-related-record's.  I'm not a filemaker expert, so I can't guarantee that's the best approach.  Anyways, I would do something similar to what I described above.  I would create 4 new fields in "other" table.  Each is a constant.  By that I mean each field simply equals a word.  In this case: 'email' 'fax' 'mobile' & 'phone'.  Name them accordingly (ie c_email, c_fax, etc...)  To achieve that you simply choose 'data' from the auto-enter options and type in the constant (ie. "email").   Name them accordingly (ie c_email, c_fax, etc...)

                         Create 4 new table occurences based on the portal table, naming each one appropriately (ie portal_email, portal_mobile, etc...).  Relate each tables to the Other table such that

                         Other::Primary_Key = Portal::Foreign_Key       AND        Other::c_Email = Portal::Type 

                         For the other three TO's replace Other::c_Email with Other::c_Phone, etc...

                         With the relationships in place, you can now use a simple script like so:

                         Set Field [Other::Email ; portal_email::info]

                         You would need four of these scripts- one for each data type.  Alternately, you could use multiple parameters to create just on script:

                         Set Field by name [GetValue ( Get ( ScriptParameter ) ; 1 ); GetValue ( Get ( ScriptParameter ) ; 2 )]

                         In which case the script parameter on the trigger would be a list like so:

                         List ( 
                         GetFieldName ( Other::Email ) ; 
                         portal_email::info
                         )

                         In summary:

                         4 new 'constant' fields in Other

                         4 new TO's based on portal related to other as: pk=fk AND constant=type

                         One 'set field' script which triggers after and event of your choosing.

                         At any rate this is how I would do it.  Perhaps someone will come along with a different approach.  Feel free to ask me any questions if you go with this method.

                          

                         Edit: actually I guess you'd just need one script without parameters if the triggering event can look them all up at same time.  Your script would just be four 'set fields'

                    • 7. Re: Script copies content from data entry portal field
                      nihmbrisby

                           Relationship would look like this attached image.

                      • 8. Re: Script copies content from data entry portal field
                        Willz

                             Thanks Nihm,

                             Here is TO's based on your suggestion. Question about _pkSeparatedID and _fkSeparatedID

                             Thinking they should be reversed in this schema - what do you advise.

                             Thank You

                              

                        • 9. Re: Script copies content from data entry portal field
                          nihmbrisby

                               Based on your current relationship graph, the 'set field' script step now has a way of 'getting' email/phone/mobile/etc...  Create a script with 5 set field steps like so:

                               Set Field [Seperated Phone Types::Email ; portal_email::contact info]

                               Set Field [Seperated Phone Types::Phone; portal_phone::contact info]

                               etc...

                               Forget about the trigger for the moment- just assign the script to a button.  Does it not work?

                               I'm a bit confused by your questions.  They suggest that the data you want actually resides in another (heretofore unmentioned) table called contacts.  My assumption was that you had a portal in a layout based on 'seperated phone types'.  This portal was viewing child records in the TO "Phone_Portal."

                               Furthermore by asking whether the pk and fk should be reversed make me wonder whether the foreign keys in your phone record portals are properly populated by the correct primary keys from separated phone types.  Seperated phone types is the parent table.  Phone portal::fkSeperatedID would already be equal to pk_separatedID in my understanding of your situation.  That would have to be the case if you were seeing multiple records from Phone_Portal in a portal placed in a layout based on Separated Phone Types.

                               Perhaps you could give me more info.

                                

                               .

                          • 10. Re: Script copies content from data entry portal field
                            Willz

                                 I’ll set up the scripts you suggest and see if that populates the separated table fields.

                                 Re: confused by questions -Yes Im confused to begin with
                                 that’s why Im on the forum. so I’ll try to explain best way I can.

                                 Yes the portal entry data actually resides in the contacts table where data is entered by the user when creating a new customer record.

                                 My new table “Phone_Portal “ is intended to mirror that data being entered in contacts table where the data entry portal is.  Don’t want to build this inside contacts table.

                                 That’s why I asked about the ID’s.  If Phone_Portal table is mirror of source portal “contacts”, seems the Primary key should be here, to generate ID for the new records that will be made in contacts.

                                 Then the Separated Phone Types gets the fk_ID looking up the ID from Phone_Portal?

                                 If I’m not thinking it through correctly appreciate your advise.

                            • 11. Re: Script copies content from data entry portal field
                              nihmbrisby

                                   I'm happy to try and help.  

                                   First of all when replying to others, I look at the "How many years have you been using Filemaker Products?" and how many posts over how many years they've made (10 years and 26 posts over 1.5 years respectively in your case).  I just want to be clear that I was/am assuming a certain degree of familiarity with ERD's in general (http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model), and filemaker's relationship model/capabilities in particular.  Let me know of I'm assuming too much or too little.  Do you have a solid grasp on how relationships work- I mean do you understand how and why to populate foreign keys with primary keys? Forgive me if the question is too basic.

                                   Consider my solution in the most general way.  A user (you) has a portal on a layout.  This user is able to view the information that we want to deal with.  In other words, the portal AND the relationships that drive it have already been successfully implemented.  This was/is my assumption based on your initial post.

                                   Each portal has one at most of any type of data.  The very fact that you are successfully viewing the records in a portal means that each record in the Phone_Portal has a foreign key which is equal to the primary key of its parent record in the "Separated Phone Types" table.

                                   The solution I offered simply builds a path so that the set field script step can access the data.  However, it assumes that in your layout based on "Separated Phone Types" you are able to view the relevant Phone_portal records in an actual portal.  

                                   If all my assumptions about your situation are correct, the method should work (and it should perform well to the extent that I have any grasp on filemaker performance).  If you can tell me where my assumptions are incorrect, I'll be better able to understand our disconnect.  

                                   Some things that are throwing me for a loop:

                              My new table “Phone_Portal “ is intended to mirror that data being entered in contacts table where the data entry portal is.  Don’t want to build this inside contacts table.

                                   Why would you need to mirror the data?  How are you mirroring the data?  If this is simply mirroring data from a table that otherwise has no relation "Separated Phone Types," then I don't see how there could by any relationship between "Separated Phone Types" and the original contacts table.  Is there currently a parent to child relationship between "Separated Phone Types" and "Phone Portal"? 

                              If Phone_Portal table is mirror of source portal “contacts”, seems the Primary key should be here, to generate ID for the new records that will be made in contacts.

                                   When you use the term portal, do you mean anything more than the layout object in which one views related records from a child table?  You'd probably better include a screen shot of this entire sector of your relationship diagram.

                                    

                              • 12. Re: Script copies content from data entry portal field
                                Willz

                                     Your question "when you use the term portal"

                                     Well if it makes difference keep in mind that the portal is a "data entry portal" it is blank until the user enters data (phone, fax, email, mobile, etc.)

                                     It 's in the Customers (contacts) table/layout.

                                     So need to get copy of that data into separate fields for a future migration to Quickbooks. 

                                     RE: your question "why mirror the portal"  May show my inexperience with ERD's not too much in 10 years.

                                     So maybe skip the portal duplicate and start connection with the source portal in customers (contacts) table?

                                      

                                • 13. Re: Script copies content from data entry portal field
                                  nihmbrisby

                                       Forget about portals, mirrroring, or any other solution for the moment.  Lets start by simply examining what you have, and determining what you want.  You have a table called contacts.   You have 5 types of contact information (url, email, fax, mobile, phone).  Do these 5 type of information correspond to 5 fields in the contacts table?

                                  • 14. Re: Script copies content from data entry portal field
                                    Willz

                                         No they do not correspond to fields in contacts table - their in a portal linked to table called "phone"

                                         Attached is screen shot:

                                          

                                    1 2 Previous Next