9 Replies Latest reply on Jun 21, 2018 2:43 PM by Sanford Whiteman

    Get Marketo Field Data Types

    Ben Neal

      I know you can export all fields and their api names and display names, but is there a way to get the data types of the fields in bulk (i.e. in a list)?

        • Re: Get Marketo Field Data Types
          Sanford Whiteman

          Yes and no.

           

          The Describe Lead API endpoint returns a datatype for all fields, but it's not actually accurate for the purpose of building an offline SQL db structure, if that's what you're thinking. (The max length of text fields isn't correct.) It should at least get you started, though.

          1 of 1 people found this helpful
            • Re: Get Marketo Field Data Types
              Ben Neal

              Sanford Whiteman Thanks for the API endpoint, I can the data types from there. I actually am exactly doing that, building a replicated SQL DB holding Lead and Activity data. Can you say more about issues that would arise with data types between Marketo and the SQL structure?

                • Re: Get Marketo Field Data Types
                  Sanford Whiteman

                  The most problematic area is the text types. If you go by the Describe response you'll end up mapping many Strings to NVARCHAR(255) and will thus lose data, since the actual capacity of many (not all, but you can't know which ones are affected) will be NVARCHAR(2000). As a result, as instances grow toward hundreds of fields (if you're not there already) you need to mark columns SPARSE b/c SQL will not allow the row width otherwise.

                   

                  In addition, you should never use Currency types in Marketo, period, since they are stored (alarmingly) as Floats. Store value × 100 in an Integer for USD (or just discard fractions entirely).

                  1 of 1 people found this helpful
                    • Re: Get Marketo Field Data Types
                      Ben Neal

                      Sanford Whiteman Thanks for the additional details, Sanford. It looks like the describe endpoint actually gives you the field length (50, 255, or 2000) so I can match my SQL fields for those different field lengths. Additionally, several of the data types could all be captured by using a NVARCHAR data type (email, string, url, phone) seeing as they're just non date or numeric fields.

                       

                      I'm actually using MySQL as the destination DB platform so I don't have the option of using sparse. I'm not as familiar with MySQL as with SQL so I'll have to research to see if MySQL has an alternative (know of one yourself?). I'm also researching character sets and going down a rabbit hole there, so I'd appreciate any other suggestion you have (seeing as your title is integration architect). Thanks again!

                        • Re: Get Marketo Field Data Types
                          Sanford Whiteman

                          I mean the max lengths returned by Describe are wrong.

                           

                          Not that they're not present.

                          • Re: Get Marketo Field Data Types
                            Sanford Whiteman

                            to see if MySQL has an alternative (know of one yourself?).

                            Well, the same-page row size limitation (iirc) in MySQL is 64K, which gives you a lot more headroom than MSSQL. You can offload data off-page using TEXT columns, **** there's no direct equivalent of SPARSE.

                             

                            I'm also researching character sets and going down a rabbit hole there, so I'd appreciate any other suggestion you have

                            In the db? UTF-8, almost certainly. Or are you working in a non-Latin context exclusively?

                            1 of 1 people found this helpful
                    • Re: Get Marketo Field Data Types
                      Steven Vanderberg

                      The Describe REST API call returns all fields with their data types http://developers.marketo.com/rest-api/lead-database/#describe

                      1 of 1 people found this helpful
                      • Re: Get Marketo Field Data Types
                        Sant Singh Rathaur

                        I don't think there is any bulk export option for this. Only way to get to know all the data types of marketo field is:

                         

                        Click on create field > Then you can see all options in the dropdown and they are:

                        1. Boolean
                        2. Currency
                        3. Date
                        4. Date Time
                        5. Email
                        6. Float
                        7. Formula
                        8. Integer
                        9. Percent
                        10. Phone
                        11. Score