not sql or odbc but ...

[ijusth]

sorry if this should go into another secttion but it does relate to external sources. I have an Excel report produced monthly that indicates all the G/L accounts that are valid. I would like to populate a database with the number in one field (about a 10 digit alphanumeric string) and the text name of that group in a second field. Yes I know the issues with creating DLL or OLE type links to an external document but to set up the SQL stuff to go into SAP and grab the live data means I need to get into security, major coding and such and I am a total NEWB. I know I can do this once a month by doing a cut and paste into a drop menu but then my concern is making sure the G/L code number and the text name stay in sync (so if that is a quicker why how do I link those two).

I appreciate any help.

[mr_vodka]

You can always import the Excel data into FM.

[ijusth]

I know I can import. Just was trying to figure out if I could reference data in a spreadsheet. If importing is the option I need to be sure that if I select a g/l number the same NAME is selected.

[ijusth]

if I import the two columns, one holding the g/l code number and the other holding the text name, into a separate database could I reference them in such a way that if I selected either one than the partner would populate the respective field? I know in theory doing a cut and paste once a month into a drop menu would give me the data but since there are so many record items I see no way to be sure that if I selected one from either category I would transfer the partner data at the ame time. In a spread sheet it is easy. You scan across the row and SHAZAM. That is sort of what I am seeking to do.

[mr_vodka]

Yes. This is of course pending that it would be unique.

For example:

GL Name

1 Red

2 Blue

3 Green

4 Red

If you choose 4, then it corresponding field would be Red, but if you chose Red, it would get the value from the first record so 1.

[ijusth]

ok the import was a relative breeze. I now have a secondary table saved under a different name (in this case test.fp7) holding just g/l and description data. Went to file/manage/database then to the assignee department field. Options gets me to the lookup choice but the test database didn't show up. I then went to the file/manage/external and added that database but I was still missing a step somewhere. How do I get to seeing the test.fp7 data from my inventory database? Hope this makes sense.

[mr_vodka]

If you have created the external data source already then go to the graph and put a table occurrence of the table from the test.fp7 file. You will see the option to change the data source option.

Then create a new layout based off that NEW table occurrence.

[ijusth]

believe me I appreciate the help but this is literally the first time I have ever worked with a database on the creation side. Other then utilizing one of those dummies for filemaker books I am a complete noob so sadly I do not know how to do the graph or table occurence you are referencing. SORRY!

[mr_vodka]

This is the relationships graph. You need to add a new table occurence ( bottom left icon ). Then specify the data source to be from the external file you have already designated.

Once the table occurrence has been put on the graph, then you can create a new layout based off that table occurrence.

[ijusth]

ok here is a screen snapshot of the graph of what I now have created (as an attachment). Hopefully it is set up right. I connected charge to network number and connected description to department (do they need separate connector line - and if so ho would you do that)? I then went into mange fields and made both ot them a lookup. I told department to look at description and charge to look at number. Now if I understand things shouldn't all of the available data points then appear in those fields? Am I missing something becuase the intent is that if I pick a name or a description then the cooresponding item needs to appear in the connected field. Our ordering system uses a number but a person would need to look for a name so they have to always be in 'lockstep'. I hope I am clear.

BTW if I haven't already said so I appreciate you efforts and patience.

[ijusth]

just wondering if there were any thoughts on this?

[mr_vodka]

See if this helps.

ChangeField.zip

[ijusth]

it looks like we are getting closer. If I understand the logic, I would inport from a csv file the names into accountinfo2 and the code into accountinfo3 (on a once-a-month basis). I can place the file you created into the same location as my main inventory datbase and then reference it ... or is it better to basically try to use the logic you made and place it directly in the main database?

[mr_vodka]

My suggestion is to read the white paper referenced in my signature file... You need to understand the difference between a table and a table occurrence. It is the biggest concept to get for a newbie.

You can import your data into a layout for either of the table occurrences. You do not have to do it twice.