11 Replies Latest reply on Jun 10, 2014 11:12 AM by Willz

    Split Portal Data

    Willz

      Title

      Split Portal Data

      Post

           Data is entered into a portal: 1st column is Drop-down selector identifies row content: Phone - Email - Fax - url

           Each a different row

           I need to split that data into separate fields.

           Do you know of a script or way to to do this?

            

           Thanks,

           WillzShire

        • 1. Re: Split Portal Data
          philmodjunk

               Split what data exactly? I don't see anything to split in your description of this issue.

               Or do you mean that the text "Phone" and the actual phone number are entered into the same field?

               You may need to provide a more detailed example of what you mean.

          • 2. Re: Split Portal Data
            Willz

                 Here is screen shot of Portal -

            • 3. Re: Split Portal Data
              philmodjunk

                   And are Phone and 434-434-4343 not already split into separate fields? They look like it in your screen shot.

                   What do you want to see instead?

              • 4. Re: Split Portal Data
                Willz

                     Phil,

                     Here is screen shot in Layout mode
                     the way this Portal was set up:
                     - "contact type" field is drop-down of : Phone, Fax, Email, url
                     - "contact info" field is the content - either of the above, each a row in the portal as entered.

                     The "contact info" field ends being populated with a mix of data, some phone, some fax, some email, etc.
                     I need to get them into separate fields somehow.

                     Thanks for you help, Will

                • 5. Re: Split Portal Data
                  philmodjunk

                       It's the "separate fields" that I don't get. Right now, they are in separate records of a related table.

                       What is the end result, exactly, that you want and why?

                  • 6. Re: Split Portal Data
                    Willz

                         Yes, you're right - they each do have a record.
                         Here is screen shot of the table view.

                         Sorry I’m not being clear , not clear myself.

                         As shown in the table view:
                         The "contact info" field ends being populated with a mix of data, some phone, some fax, some email, etc.
                         I need to get them into separate fields.
                         Re:
                         Phone field
                         Fax field
                         Email field

                         So I guess it’s combination of both  Contact Type + Contact Info and + customer name???
                          

                    • 7. Re: Split Portal Data
                      philmodjunk

                           And the question I asked earlier and which is still unanswered is "why?" why put them in different fields? (personally, I prefer to keep them just like you have them and suspect that you don't really need to put them in different fields to get the results that you want.)

                           A new question: Would this be new fields of the portal's table or the layouts?

                      • 8. Re: Split Portal Data
                        Willz

                             Thinking need to parse into separate fields because another table needs
                             phone number only.  Calling from “Contact Info” field results with mixed data.

                        • 9. Re: Split Portal Data
                          Willz

                               Not sure how that script would look like.

                          • 10. Re: Split Portal Data
                            philmodjunk

                                 Yet you aren't limited to a single relationship between a given pair of tables (this is true in all versions of FileMaker). And if you have FileMaker 12 or newer, you can use a calculation field with ExecuteSQL and a WHERE clause that specifies the type to access this info.

                                 One way to do this without using SQL is as follows:

                                 Set up this set of relationships:

                                 OtherTable::_fkContactID = Contacts::__pkContactID

                                 Contacts::__pkContactID = Phones::_fkContactID AND
                                 Contacts::constPhone = Phones::Type

                                 Phones is a new Tutorial: What are Table Occurrences? of your portal's table of phone numbers and email addresses. constPhone is a calculation field with a text result type with the quoted text "Phone" as it's specified calculation.

                            • 11. Re: Split Portal Data
                              Willz

                                   Yes I'm on Pro 12 Advanced

                                   Thank You - I see the solution you suggest with the added table, and will do that.

                                   Thanks Again for you help!!smiley