I am creating a DB from an Excel Workbook that has a lot of input fields and formulated fields. My main problem is that altogether the Excel fields add up to more than 255. My plan was initially to use one table and to create my forms and reports off that (since normal relationships won't work on SharePoint).
It seems that I am forced to use at least two tables. I am aware that I can use the Lookup Wizard to grab values from another table, but I was hoping there was a better, faster way.
At the moment I have two solutions:
1) Create one primary table with all the calculations and a reference like Date. The other table uses information that is not going to be calculated (i.e. Name, Company, etc.) and will have a field that uses the Lookup Wizard to see the primary table's Date. The Date is required forcing the user to pick a Date in order to proceed to other related forms (I am trying to avoid having them choose the date every time they open a form, so having one form only appear after another would seemingly allow this). There is then a macro that somehow goes to the Date value chosen, and then opens the next form. This would allow the other tables to use the same date for their values and eliminate any error on the user's part.
For example: The first form has a button that states "Proceed". I fill out some of the info except date. I click the "Proceed" button and it gives me an error message telling me to choose a date first. I choose a date and then click the button to advance to the next form which will not show the date, but would know from the macro that it is the same date.
I don't know how I could keep the Date value if using a macro. Does anyone know how to setup a macro to do this?
2) I create at least two tables a lot like in the first solution. I then create multiple fields as Lookup Wizards datatypes in the table with the most fields. For each of these fields I select 20 fields until I have selected all of the fields from the table with the least fields. I then create a union query to made up of all the fields in the table with the most field (including the Lookup Wizards). I then create forms and reports from this union query since it holds all the data in one place.
What I don't know about this solution is if (a) will a union query work on SharePoint? (b) Will the union query allow more than 255 fields to be used? (c) Will the union query show each of the 20 fields from the Lookup Wizard fields, or will they remain as the single value chosen?
If anyone has any better idea on how to set this up, please help! I am hoping the first solution will work, but I am not sure how to setup the macro to check for the Date chosen the previous form.
Thank you for the help.