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.
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 ]
Else If [ IsEmpty ( global ) ]
Show Custom Dialog [ "You didn't enter a type. Try again." ]
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 ]
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.