This may have to do with your data structure. All line items should be in one table. If they are in different categories, category should be a field in the line item table which is used for sorting/grouping if desired. The category might be entered via a lookup from the products table, where all possible line item sources reside for selection purposes.
You could put all four portals on one layout and set each portal say to 100 line items, then slide them up. There is the potential for errors here if you exceed the number of rows for any given portal, so you'd probably need to trap for the number of rows you're attempting to print for each portal first. Another way might be to set arrays and populate the arrays on the fly when you send the job to print.
I do agree with Stephen Huston,
Printout of related records should be done from the children side.
On the other hand as line items may have a relationship with different tables (Products, Employees, Vehicles, etc.) you should not have trouble printing out.
For this to work you need some foreign key fields in the line items table:
_kf_id_product = Products::__kp_id_product
_kf_id_employee = Employees::__kp_id_employee
_kf_id_vehicle = Vehicles::__pk_id_vehicle
and so on..
Then you may look up (copy) their values to the line item records via these relationships.
Hope this helps.
I remember once having to create a table specifically for dumping values from a number of different tables (some related, some not), creating records in the new table and then printing the records. It is not ideal, because the data is not up to date, because the new table had no real meaningful relationships going back to the source records, however, it got the desired results and once the print out was performed, the records in that table where deleted and re-populated each time the print needed to occur.
AGAIN, this sort of thing is NOT proper relational database design, but the situation was a RARE one. Sometimes, drastic situations call for drastic measures!
Thanks for all of the replies. I am struggling with how I can go about using one line item table to bring in values from the product, misc products, employees and vehicles. At the end of the day the invoice line item table will have about 7 fields, lineitem category, line item Id, Line item name, Line item description, line item quanity, line item cost, line item sale price and line item amount. The way I see this working is that I would need to populate each field with a value from one of the four above mentioned tables and perform the calculations on each of these fields based on the parameters determined in the customer setup table for labour rates, vehicle rates and material mark up. How can i make a field hold a value from four different tables? It seems as if I would need to use a value list that auto populates values from the four different tables based on my category selection, but how.
It seems I would need to do something as follows:
If Line item category = Products populate value list with data from product table
If Line Item Category = Employees popullate value list with data from product table
I just cant seem to find a way to make a value list grab data from different tables based on a calculation. It may be something simple that I am missing since I am a beginer at this.
Any Suggestions is appreciated
You need four foreign key fields matching each primary key of the other four tables.
Let's say you have an employee John Smith whose ID number is 10.
Now you create a field into the line items table called fk_employee_id.
Create a table occurrence based on the table Employees and then name it LineItemEmployee.
Then in the relationships graph make a relationship from LineItems::fk_employee_id to LineItemEmployee::employee_id.
Now put the fk_id_employee in the line items portal. Also put the related fields (from the LineItemEmployee table occurrence) such as name.
Ok, now if you type 10 into fk_id_employee you'll see the related name (John Smnith) from the employee. This is what relational means. You do not have to copy data from the relationship but just show it (unless you want it otherwise). You might use a name field in the line items table. In this case you would set up an auto-enter lookup or calculation to copy John Smith as a relationship has been stablished.
Repeat for the rest of tables (eg.: fk_id_vehicle, fk_id_product, etc.).
I hope you understand this as I am not good at English.
By the way,
You may create a value list (Employees) that grabs id_employee and name from any Employees table occurrence.
At the value list setup you can choose if you want to show one or two fields, sort by the first or second field and get values from related records only. This would be the easies way to populate fk_id_employee.
Thanks. The primary key and foreign key format is the way that I was structuring the relationships. The challenge I was having is organizing the 16 different line item fields for the four different line item categories on the layout and as well as on the print out. I think I have an I dea of how I can accomplish this, I may use one portal that is represented under 4 different tabs one for each category that way I can easily differentiate the category that they are entering data into. I hope when I put this on the print lay out I can put the products or materials on the left side and then the labour and the vehicles on the right side and then have the items shift up if the fields above them are empty. The thought is that some of the rows on the portal may have data entered for every field and some of the rows may only have data entered for the material/product fields. I will give this a try tonight and then report back my results.
It sounds like you are still planning to print from the parent record using portals.
Consider printing from the child (portal-level) record table with the items sorted in groups. It will be much more efficient.
Of course, I don't know what you want your output to look like....
I was planning on printing from the line item (child table). I am still struggling with how I can organize this so the data selection of the line items in the invoice table is done in an efficient and easy to select manner. I want to have my line item selections grouped so that the user can select the materials in one area, labour in another, vehicles in another and misc in another. I want the materials on the print out to be grouped together, labour grouped together, vehicles grouped together etc. This is the area that I struggle with the most is how can I keep the line item categories grouped together on the print out layout. This is how our customers want to see their invoices. If this can be done in a list format so that the Labour is on top, then vehicles below it and materials below it that would be acceptable. I just need to figure out how.
Thanks for your input
Actually this may be a good place to use the virtual list technique with a collector script.
This technique allows you to display data from multiple sources in any order you like.
That be my suggestion. "gather" what you may...
...and push with virtual list.
-- sent from my iPhone4 --
So create a LineItems::line_item_type_code field (mat, lab, ...) and hard code the value, and sort by that. It can also then be used as a filter.
Printing form the LineItems TOC based layout is, as others have indicated, the way to build it.
Conditional calcs in line items tables can handle the different source table data ( labour costs, item costs...)
As for adding line items to the invoice, which I assume this is referring to:
I am still struggling with how I can organize this so the data selection of the line items in the invoice table is done in an efficient and easy to select manner. I want to have my line item selections grouped so that the user can select the materials in one area, labour in another, vehicles in another and misc in another.
create a pop window, or side area on the layout ( better for windows as it avoids the adjust to size/maximise curse) , with tabs and rels for each selectable group. A simple search g_search, c_search( = g_search & "zzz") field pair related to item name keywords in each source table ( aec_name_keywords = Substitute( item_name ; " " ; "<<pilcrow character>>" ) gives searching against component words of the item names, rather than the literal name string.
As for grouping the line item prints; with a LineItems::line_item_type_code field, you could use either a sub-summary sorted by LineItems::line_item_type_code field, or posiblely multiple portals filtered by LineItems::line_item_type_code field.
IF the number of line items per invoices is limited to a small number, with roll up the latter could work.
Thanks Bruce. I have found a bit of information on your virtual list technique. As I am a beginer at this do you have any more information on the virtual list and the collector script that you could provide?.