Populate table from value list results

[simonen]

Is it possible to output the results of a value list and somehow import those into a field of another table?

[aldipalo]

I'm not sure I understand your question.

If the second table is in the same database you can just attach the list to whatever field you want it to be accessible in.

If the data is a text list any table can access it.

If the data is in a table of it's own it should be related to the table you want to access it from.

hth

[Fitch]

Are you looking for the ValueListItems function?

[simonen]

Basically, I have a table with 145,000 contacts. I have created a value list from the "Company" name field, so when a user creates a new contact, it will give them a list of companies already used in the table to keep spelling consistent.

My question is how can I put the results of that value list into another table?

For now I am just importing them over and running a script to remove duplicates, but it takes awhile to run. Just looking for an easier way.

Thanks.

[Søren Dyhr]

So all contacts usually originate from a previous tracked company?

This suggest that company name shouln't be entered as such but instead a record ID to join the person with the company, since you otherwise store pretty redundant data.

When having broken your data into a relational structure and strained for duplicates should you remember this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

For new company entries!

--sd

[Fitch]

I agree with SD, it would make sense to store Companies in their own table and assign them to Contacts via relationship based on Company ID.

But to answer your question, one option would be to sort your contacts by company name, and then export, grouped by company.

[simonen]

I think I may have misrepresented my question.

This is not a permanent table or a table that will be used in the future to select a campany to join a person to.

I am only using this to clean up the database to consolidate all that various spellings for the same organization. I like how the value list will remove duplicate spellings and I simply wanted to export that list into a table so I don't have to export all 145k into the table and run my duplicate removal script.

Basically this new table has 2 fields, old_company name and new_company name. I sort the records on the old company name and if there are 10 variations of the same company name, I paste the one I want into the new_company name field of all the ones I want to change. The table is linked through the old_company name to the original table and it is a simple "Replace Field contacts" to update all the records to the new name. Not that this matters for my question.

I am only curious to know if there is a way to populate a table from a value list result. Thanks!

[Fitch]

Exporting summarized by company as I suggested would give you the same result as the value list, I think with fewer steps. Exporting only the summarized data gives you one record for each unique value, just like a value list.

Back to your question, if you want to manipulate the data from a value list, the ValueListItems function I suggested above is the way to do it. You could set the result into a field, export field contents, then import to your table. Or you could set the result into a variable, and create new records in a loop using GetValue.

[simonen]

Nice, that sounds like what I am looking for. I so rarely use the summary functions that I forgot about it. I will check into the ValueList function as well. Thanks for the help...

[Søren Dyhr]

I so rarely use the summary functions that I forgot about it.

Ah! another Ryan Rosenberg diciple.... wrong metaphor you're leaning up against, I'm afraid!

--sd