Notice of Maintenance on October 9th. Click here for further details.

How to rename a Temp Field in a VI export?

SOLVED

Haven't found this posted before, although it may have been and my searching skills are diminished. I also understand that this might be something that I can pull into Access or Excel, or even write a Crystal Report, to accomplish. But for the moment let's just say I would like to pull this into a CSV with VI.

I'm using a Temp Field to concatenate 2 fields into one column, but I can't figure out how to give the column header a name other than Temp001. Seems like a simple thing, right?

Parents
  • +1
    verified answer

    You can do this with a temp field with perform logic or by using a file containing the perform logic.  The goal is to manipulate the cFirstRec$ variable and can be done with the SUB function.

    Given a export job set up like this.

    The Temp001 field should be set up like this.

    ""; IF UCS(ExportNamesAsFirstRecord$)=UCS("Y") AND LEN(cFirstRec$)>0 THEN cFirstRec$ = SUB(STP(SUB(cFirstRec$, "Temp002", "ProductLine"), "R", FieldDelimiter$), "Temp001" + FieldDelimiter$, "") END_IF

    Replace the Temp002 with the target temp field and replace "ProductLine" with the target column name.

Reply
  • +1
    verified answer

    You can do this with a temp field with perform logic or by using a file containing the perform logic.  The goal is to manipulate the cFirstRec$ variable and can be done with the SUB function.

    Given a export job set up like this.

    The Temp001 field should be set up like this.

    ""; IF UCS(ExportNamesAsFirstRecord$)=UCS("Y") AND LEN(cFirstRec$)>0 THEN cFirstRec$ = SUB(STP(SUB(cFirstRec$, "Temp002", "ProductLine"), "R", FieldDelimiter$), "Temp001" + FieldDelimiter$, "") END_IF

    Replace the Temp002 with the target temp field and replace "ProductLine" with the target column name.

Children
  • 0 in reply to David_Speck

    Thanks  - that seems to work, although there's a lot there that I don't understand.

    One thing I am having trouble with, and perhaps it's not a big deal, but since I am using Temp001 to run another Calculation, I am using Temp002 to rename Temp001, per your instructions. While it does rename Temp001 to what I want, I also have a blank column in my CSV file with the heading of "Temp002". This may not turn out to be a big deal, but it is at least a bit of an annoyance. Perhaps I'm doing something wrong?

    Here is what I'm working with on this. Based on another thread, and the advice of Kent, I am using Temp001 to pull out the company code (I'm using this VI job in batch files and windows tasks to run from multiple companies):

    So now I'm using Temp002 to rename the column header for Temp001:

    With this code:

    ""; IF UCS(ExportNamesAsFirstRecord$)=UCS("Y") AND LEN(cFirstRec$)>0 THEN cFirstRec$ = SUB(STP(SUB(cFirstRec$, "Temp001", "CompanyCode"), "R", FieldDelimiter$), "Temp001" + FieldDelimiter$, "") END_IF

    Which allows me to rename Temp001 to "CompanyCode", thank you again very much, but here's the result:

    Hate to be one of those beggars who is now a chooser, but... 

  • 0 in reply to rclowe

    It looks like you have changed the purpose of each temp field without updating the calculation so you are being left with it.

    As for what the calculation is doing, it first assigns a blank value to the temp field.  It then uses an IF...THEN structure to check if the option to export the column names as the first record is enabled and if the length of the cFirstRec$ variable (which holds the column names) is greater than 0.  If these conditions are met, it does the following.

    1. Uses the SUB function to substitute the string in the second argument with the string in the third argument.  In your case, it is substituting "Temp001" with "CompanyCode".
    2. Uses the STP function to strip trailing characters.  In this case it is stripping whatever character is held in the FieldDelimiter$ variable.
    3. Lastly, it uses the SUB function to replace the name of the temp field holding this calculation plus the delimiter with an empty string.

    In your calculation, you left "Temp001" in the second argument of the second SUB function (step #3 in the above steps).  Based on your fields on the Data tab, you should have "Temp002" in the second argument of the second SUB function.  

    Here is the corrected calculation for the order and purpose of the temp fields that you used.  The order of the steps have to be modified because you didn't put the calculation in Temp001.  FYI, the reason the calculation is in Temp001 is so you position it at the very top of the field list and can have as many other temp fields as needed after it and/or other Sage100 fields.  You can then use a SUB function for each temp field in the one calculation by cascading them before the STP function.  Also, the temp field used to hold the calculation should have it's operation set to Calculated so it doesn't try to output to the file.

    ""; IF UCS(ExportNamesAsFirstRecord$)=UCS("Y") AND LEN(cFirstRec$)>0 THEN cFirstRec$ = STP(SUB(SUB(cFirstRec$, "Temp001", "CompanyCode"), "Temp002" + FieldDelimiter$, ""), "R", FieldDelimiter$) END_IF

  • 0 in reply to David_Speck

    Hi David,

    Finally had a chance to test this, and of course it works perfectly.

    Thank you so much for the detailed, and totally accurate, information and steps.

    I am in awe once again.