How to combine / merge 2 value list from 2 tables

[leish2]

I am trying to combine 2 value lists into one - they are from 2 different tables and need to be use in a 3rd table.

1. TimesheetsTable = table where the TASKS value list needs to be available.

2. ProjectsTable = contains a value list of all the Tasks assigned to that Project.

3. AddendumTable = contains a value list of all the Tasks assigned to the Addendum.

• In the TimesheetsTable the person chooses a Project 123 and based on that choice a pop-up displays the list of Tasks assigned to Project 123. Works like a charm.

• Now, when we create an Addendum for Project 123 the Addendum info is in a separate table ... the Addendum can include different Tasks than the Project.

• Back in the Timesheets Table I need the pop-up TASK value list to contain both the Project Tasks and the Addendum Tasks.

Since a value list seems to require an indexed field, I cannot for the life of me find a way to create an indexed calculation that contains the related Tasks.

I came close with the this Calculation but the second value list won't work. Grrrrrrrrr....

ValueListItems ( Get (FileName) ; "Timesheet Projects Tasks" ) & ¶ &

ValueListItems ( Get (FileName) ; "Timesheet Addenda Tasks" )

Any ideas would be greatly appreciated.

Thanks, Leish

[comment]

Please describe the relationships between Projects, Addendum and Tasks (assuming there IS a Tasks table).

[leish2]

Thanks for replying ... here goes:

Here are the tables and relationships:

Projects (key field is ProjectsID_k)

ProjectsLineItems (child key is ProjectsID_kc) - this file is where the Task field is located.

• Value List - ProjectTasksVL = gets related info from the Tasks field in ProjectsLineItems starting from Projects

Timesheets (Key field is TSID_k)

TimesheetsLineItems (child field is TSID_kc)

- TimesheetsLineItems is related to Projects via the field "ProjectCode" which is entered via a pop-up list ... once this field is entered the "ProjectTasksVL" will show the correct Tasks for the Project.

- so far this all works perfectly.

However, the same process needs to be done for "Addenda" which reside in these tables:

Addenda (key field is AddendaID_k)

AddendaLineItems (child key is AddendaID_kc) - this file is where the Addenda's Task field is located.

• Value List - AddendaTasksVL = gets related info from the Tasks field in AddendaLineItems starting from Addenda

- TimesheetsLineItems is related to Addenda via the field "ProjectCode" (same as above) which is entered via a pop-up list ... once the ProjectCode field is entered then the "AddendaTasksVL" will show the correct Tasks for the Addenda.

- this value list also populates correctly.

My problem, or better stated, my required solutions will be to combine these two value lists so that there is just ONE value list that combines the "ProjectTasksVL" and the "AddendaTasksVL".

Whew, hope that makes sense. Cheers, Mike

[comment]

You didn't say how Projects and Addenda are related, but I'll assume it's a straightforward one Project to many Addenda relationship.

I think the only way you can achieve your goal is by combining the ProjectsLineItems and AddendaLineItems tables into one. This would seem, at least on the face of it, the logical arrangement anyway.

Once you have done that, you can define a calculation field in Projects =

ProjectID & ¶ & List ( Addenda::AddendaID )

and use this to establish a relationship to the combined LineItems table. This will show all items related to the project - either directly, or indirectly (through one of the project's addenda) - and therefore can be used to construct your value list.

---

BTW, you may also want to consider a single table of Projects and Addenda, with only a self-join between them.

[leish2]

Comment - You are on to something. Thanks you for opening my eyes.

Sometimes I just get so deep into the way I have designed a solution that I can't pull far enough away to see clearly.

I will consider merging the Addenda with the Projects.

BTW - you are correct about the one-to-many relationship from Projects to Addenda.

The Addenda were added after the fact so I created separate tables - the reason I did so is that they can create a bunch of Addenda but not all of them are "Active". They start as "Pending" and then move to "Active" or "Cancelled". Active means approved and the totals then get lumped into the Project totals ... pending or cancelled do not.

Also there are a bunch of Project reports and I wasn't sure that I could keep them all separate once I added the addenda into the mix.

That said, I think I probably should have used the same LineItems table - the AddendaLineItems would just be linked via a different Key Field. Can you picture me slapping my head?!

Thanks again for your help. Mike