3 Replies Latest reply on Apr 22, 2011 9:23 PM by gldiaz

    Excel List items for FMK 11

    gldiaz

      Title

      Excel List items for FMK 11

      Post

      I have no clue. Hoping someone can help.

      In Excel, I have about 10 different list, mosting inventory types, to my access.
      They have inventory descriptions, Item numbers and location. 

      I am clueless on how to setup FMK11 to call to my excel tables and fillin the FMK forms
      with my needed items.
      I really would like to avoid manually entering all 300+ items in value list.
      Is there a easier way of calling the excel list, because I may need to change or update
      items in the future.

      So while in FMK, I would like to seek inventory items already built in Excel and paste only
      the needed items into FMK.
      Is this possible?

      Hoping this gives enough info.

      Tks in advance.

      GD

        • 1. Re: Excel List items for FMK 11
          bumper

          The easiest way to get started it to let FMP convert the Excel file for you. 

          From the menu File, select Open at the bottom of the dialog box select Excel (note there are two versions of Excel offered) select your Excel file and let 'er rip. You have a choice of naming the new fields if you have the field name already in the first row of your Excel file or FMP will just name the f1, f2, etc and you can rename them later. Once you have converted the Excel file you can do anything with it you that you would any FMP file, you can relate it to your Main file or if you have FMPAdvance you can copy and paste the table and then import the data, etc. and later make changes to the data

          I can't really tell from your post exactly what you need to do with with it in the future, more info. But once you have the data into a filemaker file you may want to consider making your changes in the FMP file and bypassing the Excel one.

          • 2. Re: Excel List items for FMK 11
            LaRetta_1

            I would suggest that you have an Items table (or call it Products).

            "10 different list, mosting inventory types, to my access.
            They have inventory descriptions, Item numbers and location. "

            Not enough information but I would think all 10 lists can be in the same table with the following fields:
            Type, Description, ItemNumber and Location

            You then use this new Products table to relate through your FileMaker solution.  Whenever you wish to update your Products, you would run a script similar to:

            Go to Layout [ layout based upon Products ]
            Show All Records
            Import [ here you select/specify the Excel file and map the fields.  Set to UpDate (and maybe Add To if you have new products coming from Excel and set the Item Number as the =. ]

            In this way, it will update your existing information in your Products table.  You didn't say if there was a Type field being imported.  If so, just import it.  If not, you will need to assign the type upon inport?  If the Excel file tells you the Type that you are importing, then you can use Replace Field Contents[] either manually or via script at the end of the script I suggested with the additional following lines (create a global text field if you don't have one already somewhere that you can use):

            Show Custom Dialog [ OK ; Cancel ; "Enter the Product Type that you are importing" ; Select the Input tab and set input Field1 to this global field.]
            If [ Get ( LastMessageChoice ) = 2 // user selected cancel ]
            Halt Script
            Else If [ IsEmpty ( global )  ]
            Show Custom Dialog [ "You didn't enter a type.  Try again." ]
            Halt Script
            End If

            You should have a value list called Types.  It works best to use values from a field instead of Custom.  In this way, you can add a value to the field and have it appear in the list.  But you must control who uses/changes this field and I won't go into that right now.  So the last piece of the script should be to test whether your entry is valid and if not, as you if you wish to allow it anyway (and it would now be added to your Types values).

            If [ IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "Types" ) ; global ) ) // invalid entry ]
            Show Custom Dialog [ Add ; "This Type is invalid. Correct it or Add it."  Again display input1 with the global which will allow you to correct your entry or add a new Type to your list ]
            Replace Field Contents [ by calculation ; Products::Type ; global ]

            As Bumper says, more information would help but these additional pieces should get you moving ahead.Smile

            • 3. Re: Excel List items for FMK 11
              gldiaz

              Thank you!

              Thank you!

              Thank you!!

              I've definitely learned that you can do something to have a active excel table and use it

              in FMK to get possible desired outcomes.

              Bumper and LaRetta, Nice job.

              Now that I have something to work with,  I also have a "Backup or set" of options to try.

              Thank you both.

              Respectfully,

              GD