Split existing record according to multiselect Dropdown and copy to different table (1 Viewer)

[Michael Barkemeyer]

Hi everyone,

Background:
I am trying to simplify invoice entry.
The user enters the activity to be invoiced using form: "ServiceCharges"
The invoice activity for each record could potentially be charged across multiple client projects. To do this, the user selects one or various client projects from the multiselect dropdown box.

The Ask:
How do I copy all the records to a new table (where I can then generate individual Invoices), while at the same time splitting out those records that have multiple client projects associated to them?
As an example: If record #1 on form: "ServiceCharges" has 3 projects checked in field: "Projects", then add 3 records in table: "SplitOutServiceCharges" using each one of those projects.

Thank you for your help!

 

[moke123]

First thing I would do is to get rid of the Table level look up fields and the multi-valued fields in your tables.
Google "the evils of lookup fields"

 

[jdraw]

Totally agree with moke123, and would add that, instead of talking in terms of tables and database, describe in plain English the issue you are dealing with in business/plain English terms. There are free data models at Barry Williams' site that may be helpful to you in putting the "pieces" together.
Customers and Invoices
Invoices and Payments

 

[zeroaccess]

I think you should be able to achieve individual invoices without moving data to different tables. For example, open your invoice (report?) with a WHERE condition to specific ID's.

 

[Michael Barkemeyer]

Thank you everyone for your suggestions.