FM6: Populate multi-value list from multiple records

[intercom]

Sorry to still be using FM6. It's the only one that runs on OS9, OSX and Win32.

I have a Detail DB where each record is one billable item in a single job, with a JobID.

For the invoice I need to display all of the individual items on separate lines, and want to copy the data rather than leaving it hotlinked.

I _THINK_ the easiest way would be to make a multi-line text field and read in the fields from all the records with that particular JobID, but the multi-line field will only read in the first record it hits, no matter what I try.

I am still searching but have yet to find a promising idea...

Any assistance gratefully accepted!

Edited December 22, 2006 by Guest

[intercom]

I can read the necessary data into a popup list, popup menu, checkboxes or radio buttons, so that every one of the individual field entries from the details DB is shown as in the popup on every line.

I can't find a way to grab the same data and just show one per line in the multi-line field, though.

[Ender]

The line items for an Invoice should be related records in a Line Item file. They would be related to the Invoice via the Invoice#. You can then use a portal to show multiple line items on the data entry layout, and use a sub-summary report based in the Line Item file for the printout.

[intercom]

Thanks for the tip. Unfortunately, I've already got that working. Let me be more specific.

There are basically three files involved. I'll skip the reasons why.

1. Details: Each record is one item on a customer job, and includes RecordNo, JobNo, and a number of individual data items a,b,c...

2. WorkRecord: A portal showing all of the details for that specific JobNo. Also has a unique RecordNo

3. Invoice: We need to call in all of the detail records for a specific JobNo, but we ALSO need to be able to edit them without affecting the underlying Detail or WorkRecord records. This is why I think it would be best to be able to create an Invoice record with a unique RecordNo and a specific JobNo, and have multi-value lists a,b,c... which are populated by items a,b,c from the Detail records for that same JobNo. If the data can be copied from the Detail fields into individual lines of the list, it can then be revised with no effect on the source data.

Does all that help?

If the above is not reasonable, I'll have to create an InvoiceDetail DB and just copy over all Detail data to it, then edit the InvoiceDetail DB as appropriate. I would rather use the former approach, though.

[Ender]

Sorry, your description only made the structure harder to understand. It's not clear why all three of your files would need a RecordNo and a JobNo, or which is supposed to be the primary key and which is supposed to be the foreign key in each. And what happened to the Job DB that you started out talking about?

[intercom]

The JobID is the identifier for a single job, and therefore a single invoice. The JobID is used to mark which job each individual Detail record belongs to, and is the ID of the larger WorkRecord which shows all the Detail records in a portal.

For the Invoice, I want to find all Detail records with a given JobID, and load fields a,b,c... directly into multi-value fields a,b,c in the Invoice record. One invoice record would therefore contain the same data as the WorkRecord, but the data would no longer be connected to the Detail records, and could be edited without affecting them in any way.