9 Replies Latest reply on Jul 24, 2012 5:33 AM by MarcMcCall

    Help modifying a custom function

    MarcMcCall

      Title

      Help modifying a custom function

      Post

      Hey guys,

       

      I need a little help modifying this custom function.

       

      // ExtractData ( XML, Attribute, Instance ) 

      Let ( [
      XML_Length = Length ( XML ) ;
      Attribute_Length = Length ( Attribute ) ;
      Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance )
      ];
      Substitute(
      Case(
      IsEmpty ( XML ) or IsEmpty ( attribute ) or PatternCount ( xml ; "<" & attribute & ">" ) = 0 ; "" ;
      Middle ( XML ; Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + attribute_length + 2 ;
      xml_length - ( xml_length - Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance ) ) - ( Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + Attribute_Length + 2 ) )
      ) ;
      ["&amp;";"&"];
      ["&lt;";"<"];
      ["&gt;";">"];
      ["&quot;";"\""];
      ["&apos;";"'"]
      ))
       
      I am using it in a field like this:
      IdCustomer    calculation = ExtractData ( result ; "IdCustomer" ; EventNumber )
      The EventNumber field I use to change numbers in place of the instance to get the next set of data from the XML. the only problem is, is if the 
      instance is empty, it gives me the next instance of info. So if I have an EventNumber of 8, and the IDCustomer instance for 8 is empty
      it gives me instance 9, or the next one with data. I want it to give me the empty data, so I can keep the empty data with the correct record. I use this 
      in a solution that accesses a web service, and this particular one pulls info from a website as ViewAllCustomers, in one very long 
      xml file. I have a script that starts at instance 1 and uses set variable on all fields, gos to another layout and creates a record from the parsed 
      data, gos back to the origianal layout and adds 1 to the EventNumber to view data of the next record and gos to the other layout to create a new record.
      When a particular field is empty, the loop stops.  
       
      Any assistance will be much appreciated.

        • 1. Re: Help modifying a custom function
          philmodjunk
          I think this will work. Position returns 0 if the specified instance cannot be found so you can check the value of that result and return "" if that's the case.

          Let ( [
          XML_Length = Length ( XML ) ;
          Attribute_Length = Length ( Attribute ) ;
          Get_Instance = If ( IsEmpty ( Instance ) ; 1 ; Instance ) ;
          Att_Pos = Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance )
          ];
          Substitute(
          Case(
          IsEmpty ( XML ) or IsEmpty ( attribute ) or Att_Pos = 0 ; "" ;
          Middle ( XML ; Position ( XML ; "<" & attribute & ">" ; 1 ; Get_Instance ) + attribute_length + 2 ;
          xml_length - ( xml_length - Att_Pos ) - ( Att_Pos + Attribute_Length + 2 ) )
          ) ;
          ["&amp;";"&"];
          ["&lt;";"<"];
          ["&gt;";">"];
          ["&quot;";"\""];
          ["&apos;";"'"]
          ))
          • 2. Re: Help modifying a custom function
            MarcMcCall

            I copied that straight into the function, and tried it, and it returns all fields empty.

            • 3. Re: Help modifying a custom function
              philmodjunk

              This looks a bit curious in your original function:

              Position ( XML ; "</" & attribute & ">" ; 1 ; Get_Instance )

              Is that / correct or a typo?

              I completely missed it the first time around....

              It might help to post a sample of your XML data.
              • 4. Re: Help modifying a custom function
                MarcMcCall

                I copied it straight from the custom function.  How about a sample file.

                 

                http://www.mediafire.com/?wgwatcwwlh64o6h

                 

                Fixed link.

                • 5. Re: Help modifying a custom function
                  MarcMcCall

                  Phil,

                   

                  I found the problem.  Its not the custom function, it is the XML data set.  What is happening is that if the field is empty on the site, the XML doesnt return the attribute in that data set, and the function is extracting the instance it is told to, it just happens to be from what would be a different account.

                   

                  I guess I am goint to have to try a different approach.  Is there a way to exrtact the data between the lines in red (below) and use an EventNumber to change the Number at the end of "ViewAllCustomers1"  to "ViewAllCustomers2" to get the next set of data?

                  <ViewAllCustomers diffgr:id="ViewAllCustomers1" msdata:rowOrder="0">
                  <IdCustomer>1</IdCustomer>
                  <IdCustomerType>2</IdCustomerType>
                  <IdUser>1</IdUser>
                  <IdAddress>1</IdAddress>
                  <IdRepCode>0</IdRepCode>
                  <IdAffiliate>0</IdAffiliate>
                  <IdStore>0</IdStore>
                  <IdCustomerGroup>0</IdCustomerGroup>
                  <gender>9</gender>
                  <name>Marc</name>
                  <company_name>company</company_name>
                  <tel>(888)888-8888</tel>
                  <fax/>
                  <mob/>
                  <email>marc@email.com</email>
                  <password>1234</password>
                  <internal_account>511</internal_account>
                  <points_active>0</points_active>
                  <points_accumulated>0.0000</points_accumulated>
                  <points_rate>0.0000</points_rate>
                  <points_rate_amount>0.0000</points_rate_amount>
                  <shipping_override>0.0000</shipping_override>
                  <shipping_override_status>0</shipping_override_status>
                  <tax_override>0.0000</tax_override>
                  <tax_override_shipping>0</tax_override_shipping>
                  <tax_override_status>0</tax_override_status>
                  <cheapest_price>0</cheapest_price>
                  <exclude_global_contract>0</exclude_global_contract>
                  <cataloug_restrict>0</cataloug_restrict>
                  <receive_newsletter>0</receive_newsletter>
                  <enforce_po>0</enforce_po>
                  <hide_price>0</hide_price>
                  <hide_save>0</hide_save>
                  <hide_buynow>0</hide_buynow>
                  <date_registerd>2012-03-09T13:47:19-06:00</date_registerd>
                  <date_last_login>2012-07-23T09:48:53-05:00</date_last_login>
                  <date_last_order>2012-07-19T08:46:36-05:00</date_last_order>
                  <active>-1</active>
                  </ViewAllCustomers>
                  <ViewAllCustomers diffgr:id="ViewAllCustomers2" msdata:rowOrder="1">
                  • 6. Re: Help modifying a custom function
                    philmodjunk

                    Have you tried doing an import of this XML data into a table in your database? That strikes me as an approach that might be much easier to deal with.

                    Let me see if I Have this straight:

                    You want all the data between the lines starting with:

                    <ViewAllCustomers

                    and

                    </ViewAllCustomers

                    Correct?

                    (See where I'm headed with this in the CF?)

                    • 7. Re: Help modifying a custom function
                      MarcMcCall

                      Yes.

                       

                      the <ViewAllCustomers diffgr:id="ViewAllCustomers1" msdata:rowOrder="0"> is the start of the first record, and </ViewAllCustomers is the end of a record. then the next record will be <ViewAllCustomers diffgr:id="ViewAllCustomers2" msdata:rowOrder="1"> and end with </ViewAllCustomers so on and so on....

                       

                      No, I havent tried importing,  I have a plugin "WinFmNexWeb"  that access the web service of the website and enters the XML response into the field "result"  then I simply change the EventNumber field, to view the next data set from the XML.  this is where I was going to have the script loop through and set variables and goto another layout and create an individual record from the parsed data, till I ran into my problem.  I discected a sample file I found and addapted it for my purposes.  
                      Most of what I use it for is to actually post data to the site, but I am trying to make this work, so I can update my solution with the website generated data for the accounts, IE, customer #'s and other ID#, generated by the site.

                      • 8. Re: Help modifying a custom function
                        philmodjunk

                        It might be an interesting experiment to use Export FIeld Contents to export this data to a file and then do an xml import into a temp table in your database. I haven't messed around with XML imports, but it sounds worth a look.

                        I don't have time to write the CF at this time, but what I'm thinking here is to set up the calculation to look for

                        <ViewAllCustomers

                        First, then find the next < as the start of the data to be parsed, with

                        </ViewAllCustomers

                        as the flag marking the end of the text to be parsed.

                        • 9. Re: Help modifying a custom function
                          MarcMcCall

                          I figured out an easy solution. I used an auto replace calculation. To add the elements if they arent in the XML, and this will add them in every data set in the XML if there is no data coming from the site.  

                          Substitute(result;
                          ["<tel />"; "<tel></tel>"] ;
                          ["<fax />"; "<fax></fax>"] ;
                          ["<mob />"; "<mob></mob>"] ;
                          ["<email />"; "<email></email>"] ;
                          ["<password />"; "<password></password>"] ;
                          ["<internal_account />"; "<internal_account></internal_account>"] ;
                          ["</password><points_active>"; "</password><internal_account></internal_account><points_active>"] ;
                          ["</active></ViewAllCustomers>"; "</active><external_id></external_id></ViewAllCustomers>"] ;
                          ["<external_id />"; "<external_id></external_id>"]
                          )