Seems like you should have more than one table, but not so that each spreadsheet is imported into different tables. I'd define a table for companies where I'd have one record for each company. I'd either put the check box field their or use a third related table in place of the check box field. Which method I'd choose would depend on how I need to use the data put in that field.
This approach would require importing a value from the spreadsheet that uniquely identifies each company. Names are not the best option, but if you have no control over the content of the spreadsheets, it may be your only option here. I'll assume you have a better ID such as a TaxID number for this approach.
Companies::CompanyID = SpreadSheetData::CompanyID
With this structure, I can then set up a script that loops through the data each time you import a spreadsheet and updates either the checkbox field or that third related table by adding an entry identifying the spreadsheet if it is not already present in the check box field or set of related records.
Thanks for the response. The companies are publicly traded, so the unique identifier is their symbol. The purpose of keeping track of which spreadsheet produces which company is to get an idea of what produces the best results (ie, profit, number of buys, etc.). That part I am still at the drawing board with, but will be a bit contingent on the initial setup.
So given that, I would create a table solely for the companies and have the checkbox set as a field in that table. Or a related table? If I go with a related table then there would be a company table, a table with spreadsheetdata (which would be use to identify the spreadsheet the company was listed on?), and would there be any other tables? I know I would need more for whatever I want to analyze as well.
With either approach, you start with two tables, 1) a table of companies, one record for each company. You'd add any fields to this table that you need that store data unique to a specific company. For just the task at hand, I'd use fields for TradingSymbol, __pk_CompanyID (auto-entered serial number) , CompanyName, and then a text field for the checkbox group if I decided to use that method. 2) a table for the spreadsheet data.
The choice is whether or not to add a third table for listing links between company in place of the check box group field. I have no way to tell at this point which is the better option, but how you set this up is much the same.
Here's a script based on the check box group field method:
First, define this relationship:
Companies::TradingSymbol = SpreadSheetData::TradingSymbol
Immediately after import, the import records action produces a found set of the new records created or updated by the import. The script would run on this layout:
Go to Record/Request/Page [first]
#Check to see if checkbox is not already selected
If [ Not valuecount ( FilterValues ( Companies::CheckboxField ; SpreadSheetData::SpreadSheetID ) ) ]
#Add spreadsheetID to list if not already selected in checkbox group
Set Field [Companies::checkboxField ; List ( Companies::CheckboxField ; SpreadSheetData::SpreadSheetID ) ]
Go to Record/Request/Page [next ; exit after last ]
This script assumes that you have a column in your spreadsheet that stores the value you want to select in the check box group. You'll need to modify this script if this is not the case.
Ok, PhilModJunk I will give it a try and let you know how it goes. Thanks!
I've experimented with it, and I am a bit lost. What would I be importing into the second table? Also, I have validation setup on the symbol field to prevent duplicate company entries. Will this pose a problem with the operation?
A validation on this field in which table? Companies or spreadsheet data?
To generate records in Companies such a validation is exactly what you need. You can then import from a spreadsheet into this table and the validation rule can allow the first instance of each companie and prevent the duplicates from importing.
Also, the above script can create a new record each time a spreadsheet record is encountered that does not have a matching Companies record, if in the relationship between the two tables, "allow creation of records via this relationship" is specified for the companies table. In that case, the set field step will create such a new record if one does not already exist. (no data except the the trading symbol and the checkbox field will have any data in this new company record unless you define looked up value settings on additional fields to copy data from the spreadsheet table.)
The validation is on the Companies::Symbol field. It is validate data in this field always and require a unique value.
I will make that change to the script to allow creation of records via this relationship.
So to recap just to ensure I understand:
1. I would utilize the Companies table as the basis for everything, in that it would have all the fields and tables that I would use to do my later analysis. So, would that mean I would be importing directly into that table? I wasn't sure if that were the case given that the validation would prevent any new records from being created if a company already exists.
2. What exactly would the second table be for? I know you said spreadsheet data, but given that I am importing into the first, I am just not seeing what the second would be for.
(Sorry, for a minute I was following you, but I am just perplexed on the second table and to which the importing should be done.)
1) I wouldn't do that. The Companies table should only contain fields for the stock symbol, company name and any other fields for which you only need to record data once for each company. Your spread sheet data table will contain the fields where data for the same company must be recorded more than once. If you want to, You can import the entire spreadsheet of data into this table, but only map to the fields I just described. The validation rule will filter out the duplicates to give you one record only for each company. This is an option. You do not have to use this method. By enabling "allow creation", the script will add new companies to the companies table each time a record for a new company is imported. The limitation here is that the new company record will not contain a company name unless you add a set field step to update the company name field at the same time that you set the CheckBox Field.
2) I've answered that question in my response to question 1. Here's an example using made up figures. Hopefully you can see how it matches to your actual situation:
Say each row in your spreadsheet had these three columns: Symbol, CompanyName, MonthlyEarnings.
You'd define fields in Companies for Symbol and CompanyName. You'd define fields for Symbol and MonthlyEarniings in SpreadSheetData.
Symbol is defined in both tables to provide the common value you need to match records in your relationship. CompanyName is defined in Companies as you only need to record the name of the company once, no matter how many months worth of data is imported. MonthlyEarnings is a value that will change every month and thus needs a different record for each month, for each company. Thus you import it into the spreadsheetData table, but include the trading symbol to link it to the correct record in Companies.
This gives you this relationship, by the way:
Companies----<SpreadSheetData (---< means one to many)
Companies::Symbol = SpreadSheetData::Symbol
And a portal to SpreadSheetData can be placed on a Companies based layout to list all the srpeadsheetData records with a matching value in Symbol.
Ok, thank you. It all makes sense now. Thanks again for all your help. I really appreciate it.