9 Replies Latest reply on Aug 26, 2011 2:53 PM by philmodjunk

    So not being well versed in the ways of FM I need some assistance to create an excel upload file....

    mfl666

      Post

      So not being well versed in the ways of FM I need some assistance to create an excel upload file. This file is for our ERP system which is not FM.

      The setup I am working with is .....

      DB/Tbl – STYLE_MAIN, all garment styles contained in a “Program”.

      Tbl – STYLE_ClrSelect, all colors associated with a particular style, there can be a max of 8. There is one record for each color of a style.

      Tbl – ZZSTYLR, file format for upload. Contains all the fields that are in a specific order for the upload. Related to STYLE_ClrSelect by style serial number.

      The current script I have creates the correct number of records in the excel file but it only ever puts in the first color number for all the records. For the most part the only thing that changes is the color#.

      This process also only works if its a new style setup or if I leave the style and go back in. I have also set ZZSTYLR to create and delete records when created in ClrSelect.

      Current script...

       

      Set Variable [ $filename; Value:Style_Main::Style_ID_Name & "_export.xls" ]

      Set Variable [ $pcdesktop; Value:Case ( Left ( Get ( DesktopPath ) ; 2 ) = "/C" ; "filewin:" & Get ( DesktopPath ) & "Style PDFS/" & $filename & "¶" & "filewin:" & Get ( DesktopPath ) & $filename ;

      Left ( Get ( DesktopPath ) ; 2 ) = "/U" ; Get ( DesktopPath ) & "/" & "Style PDFS/" & $filename & "¶" & Get ( DesktopPath ) & "/" & $filename ;

      Left ( Get ( DesktopPath ) ; 2 ) = "/M" ; Get ( DesktopPath ) & "Style PDFS/" & $filename & "¶" & Get ( DesktopPath ) & $filename ) ]

      Set Field [ ZZSTYLR::BCDivision; "MON" ]

      Set Field [ ZZSTYLR::BCStyle_Type; "FG" ]

      Set Field [ ZZSTYLR::BCStyle; Style_Main::Style_ID_Program_Code & Style_Main::Style_ID_Style_Number ]

      Set Field [ ZZSTYLR::BCStyle_Name; Style_Main::Style_ID_Name ]

      Set Field [ ZZSTYLR::BCMin_Multiple; "Y" ]

      Set Field [ ZZSTYLR::BCStyle_Avail; "Y" ]

      Set Field [ ZZSTYLR::BCActive_Ok; "Y" ]

      Set Field [ ZZSTYLR::BCStyle_Desc; Style_Main::Style_Description ]

      Set Field [ ZZSTYLR::BCSize_Code; Style_Main::Style_Size_Range_Code ]

      Set Field [ ZZSTYLR::BCColor_Code; Style_ClrSelect::Style_Color_Standards_Legend_NRF_Num ]

      Go to Related Record [ From table: “Style_ClrSelect”; Using layout: “ZZSTYLR” (Style_ClrSelect) ] [ Show only related records; New window ]

      // Go to Layout [ “ZZSTYLR” (Style_ClrSelect) ]

      Save Records as Excel [ File Name: “$pcdesktop”; Records being browsed; Worksheet: Style_Main::Style_ID_Name; Title: "Style Code: " & Style_Main::Style_ID_Name ] [ Restore; No dialog ]

      Close Window [ Current Window ]

       

       

      The other option I was thinking of was to add all the fields I have in ZZSTYLR and add them to ClrSelect. I already have a layout that gives me the correct records and color #'s, it would be just a matter of adding the Set Field statements for the remainder. I just not sure if this would be the most logical approach.

      All ideas welcome.

      Thanks

      Michael

        • 1. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
          philmodjunk

          What layout is current when you run this script?

          How are your tables related?

          What format do you need in your excel file? (what do you need in each row and column?)

          I suspect that you can export records to Excel directly from a child table here ( Style_ClrSelect?) without using ZZSTYLR at all, but need to know the format of your excel files to be sure.

          • 2. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
            mfl666

            Sorry got dragged away to do something more "critical".

            In answer to your questions...

            The script is run from the style layout which has all the relevant style info on multiple tabs.

            Style_Main, Style_ClrSelect, and ZZSTYLR are all related through Style ID.

            I currently have a button on the Color tab which takes me to another layout which gives me all related info for that Style/Color. I had put these because we had installed SyncDek and I needed to verify detail data with our China DB. If I have say 3 colors then this layout gives me 3 records which is basically what the excel file should be. The columns are in a specific order plus I need to add some field data that is not in our DB.

            Hope this is a little clearer.

            Thanks

            Michael

             

            • 3. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
              philmodjunk

              You appear to have this relationships:

              Style_Main::Style ID = Style_ClrSelect::Style ID

              What's not clear is which of the following are defined in your database:

              Style_Main::Style ID = ZZSTYLR::Style ID

              OR

              Style_ClrSelect::Style ID = ZZSTYLR::Style ID

              "The script is run from the style layout..."

              If you go to this layout, enter layout mode and select Layout setup, what is listed in "Show Records From"? Style_Main, Style_ClrSelect or ZZSTYLR?

              "If I have say 3 colors then this layout gives me 3 records which is basically what the excel file should be. The columns are in a specific order plus I need to add some field data that is not in our DB"

              From that, I'm guessing that your excel file should look something like this:

              Style ID, //other fields as needed from Style_Main , Style_ClrSelect::Color , //other fields from Style_ClrSelect as needed.

              For the purposes of this discussion, it's not important what the left to right order to your columns is, but rather that you want one row for every record in Style_ClrSelect that also includes data from the related Style_Main record in every row as we can easily control the left to right column order in the field mapping portion of Export Records. Am I correct as to what makes up each row of your Excel file?

              If I have interpreted all of this correctly, there is no need for the ZZSTYLR table if its only purpose is for exporting data to this excel file. You can export this data from a layout based on Style_ClrSelect (This is the name you should see in "Show Records From" in Layout Setup...) with a single script step: Export Records--where you can specify the fields and the desired left to right column order to match the requirements for you Excel file.

              • 4. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                mfl666

                The relationships run like this:

                Style_Main::Style ID = Style_ClrSelect::Style ID = ZZSTYLR::Style ID.

                The relationship between ClrSelect and ZZSTYLR is "=" with allow creation and delete in ZZSTYLR.

                Ok so far so good.

                I selected the fields I need from Style_Main, ClrSelect, and ZZSTYLR.

                I ran into a problem, I have 3 different color records but I need to concatenate "M0" to the color codes.

                The result was that the process gave me 3 records for export but it took the first color record and put it into all 3 as "M0123".

                Should I be adding all the fields I need for export to my Style_ClrSelect table to simplify things.

                • 5. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                  philmodjunk

                  I don't see where you need the ZZSTYLR table for this export. What data exists in that table that you need to export?

                  You can append "M0" to your color codes by defining a calculation field to combine M0 with your codes and then you export that field instead of the color code field.

                  From what layout did you export your records? On what table was it based?

                  Assuming that your color fields are defined in Style_ClrSelect, you should export from a layout based on that table.

                  • 6. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                    mfl666

                    Ok got it working, thanks.

                    On another note I have an "IF" that checks for valid fields in the same upload process......

                    If [ IsEmpty ( Style_ClrSelect::Style_Color_Standards_Legend_NRF_Num ) or IsEmpty ( Style_Main:: Style_Size_Range_Code ) ]
                    Show Custom Dialog [ Title: "STYLE UPLOAD ERROR"; Message: "The style promotion to BlueCherry will be terminated because...¶
                    1. One or more of the colors do not have relevant NRF color codes.¶ 2. Size Code does not exist.¶ Please fix before attempting to upload."; Buttons: “OK”, “ ” ]
                    Exit Script [ ]

                    End If

                    Unfortunately it only sees the first record and proceeds to create the excel file if there is a value in it, it's possible that any records following could be blank.

                    Is there a calc that will allow me to check all the records before proceeding?

                    • 7. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                      philmodjunk

                      What layout are you on when this script executes? (This is a crucial detail to keep track of for all your scripts.)

                      • 8. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                        mfl666

                        The button is located the "show records from" Style_Main layout.

                        • 9. Re: So not being well versed in the ways of FM I need some assistance to create an excel upload file....
                          philmodjunk

                          If I have this right, you are exporting all the colors for just the current record in Style_Main, correct? If so, this issue only lies with the check on the first field in the If step: Style_ClrSelect::Style_Color_Standards_Legend_NRF_Num

                          Open Manage | Database | Relationships, find the relationship between Style_Main and Style_Clrselect and double click the relationship line to bring up the dialog for setting relationship options. Select the sort option and specify that Style_ClrSelect be sorted in ascending order by Style_Color_Standards_Legend_NRF_Num. This will make any record with no value in this field the first such related record. (This change could affect the results produced by Go TO Related Record script steps and portals to this Style_ClrSelect.)

                          Now your IF step in the script will execute correctly.

                          Note: if setting up this sort order creates problems for other parts of your system, you can create a new occurrence of this table, link it by the same fields and specify the sort order in this new relationship. Then update the If statement to test the field from this new occurrence.