Combining 2 Tables into one layout

[Colby0]

Hi!

 

I am looking for some help on a layout.

 

I am working to create an invoice from 2 sets of data.  What I have now are 2 tables, one for services, the other for products.  These were imported from an old Paradox system.  I am held up creating an Invoice layout that shows records from both of these.

 

I have a table called "trans" which is a Invoice table.  This is where all of my invoice info is, this includes things like shipping, tax, employee times and etc.  In this table I have product and service totals from my product and service tables. 

 

Normally I would create a layout from product and put my invoice data in the header and footer and do a summary at the bottom to show invoice totals.  I can't do this in this file because they have 2 tables with line item detail for this one invoice. 

 

What is the best way to create this layout?  I thought about creating a new table that stored both the products and services and would copy them whenever a new line is entered, but this isn't very clean. 

 

I know it can be done, but I am having a hard time.  Any input would be greatly appreciated.  If you need anything else to help me please let me know.

 

Thanks

Cole

[comment]

This part is not clear:

 

I thought about creating a new table that stored both the products and services and would copy them whenever a new line is entered

 

Are you dealing with legacy records only? Or will you be getting new lines of both types - if so, how exactly?

 

--

P.S. Are you really using version 7?

[Colby0]

I am importing old data from a paradox 4.0 system but I will be adding new records moving forward.  The Paradox system is from 1992.  I am using FM 13.

 

If I didn't have to continue adding records I would add both the services and product into a single table, but since I have to continue I need to figure out how to make it work.  If I can't make this work I can restructure everything but there are 150K records so that would take a long time.  Also, there are duplicate item numbers (relationship key) in both the services and products.

 

Right now I have a layout in my "trans" table.  This is my invoice level table.  In this layout I have 2 portals one for services, the other for products.  I can add records to the service and product tables via these portals.  It works pretty well in similar files I have created, but I have never needed to have 2 tables associated with the invoice.

 

I have attached a screen shot of the invoice entry layout.  The services are in the visible tab, the product is almost identical in the next tab.

 

Again, I am looking to add both of these tables to a single report to show an invoice with both records on it.  I can make one or the other work perfectly, but I am stumped on how to show both.

 

Thanks so much for your help.

 

[comment]

You shouldn't have any problems continuing their two-line-items-tables arrangement, except when it comes to printing. Filemaker does not do a union join natively. If you cannot be sure that all items of both types will fit on a single page (using two portals with sliding), you will have to find some other trick for it - for example, use SQL or a dummy table to unite the line items ad hoc for the printout.

I am using FM 13.

 

Please update your profile to reflect your current version and your OS.

[LaRetta]

It would sure be easier if you merge to a single table.  A Service *is* a Product (usually) such as Mechanic Rate = $35.00 per Hour, etc and can be added onto an Invoice as a LineItem just like any other product, charge, payment, NSF etc.  Just a consideration. :-)

I have a table called "trans" which is a Invoice table.  This is where all of my invoice info is, this includes things like shipping, tax, employee times and etc.  In this table I have product and service totals from my product and service tables. 

 

This IS your LineItems I believe and you lack an Invoice table.  Products and Services should be a single 'sellable' product which become records in LineItems when added onto an Invoice.

 

Just some thoughts on it ...

[comment]

A Service *is* a Product (usually) such as Mechanic Rate = $35.00 per Hour, etc and can be added onto an Invoice as a LineItem just like any other product,

 

It's not that I disagree - but I would ask what is the difference, in terms of fields, between the two tables before making such a decisive statement. I should have asked this too, instead of assuming there was a legitimate reason for this separation to begin with.

 

Anyway, if your assumption is correct, then 150k records would not be too many to make a one-time migration. However, there is this:

 

Also, there are duplicate item numbers (relationship key) in both the services and products.

 

I think it means that there are not only two "line items" tables, but also two "products" tables - and that there can be a collision between ProductID and ServiceID. So this migration could be more complex (read: error-prone) than it might seem. 

 

 

 

This IS your LineItems I believe and you lack an Invoice table.

 

I hope not, because then ...

[LaRetta]

It's not that I disagree - but I would ask what is the difference, in terms of fields, between the two tables before making such a decisive statement. I should have asked this too, instead of assuming there was a legitimate reason for this separation to begin with.

 

When it comes to financials, having the money in the same pot (LIneItems) makes summarising, reporting and account adjustments simple as you know, Michael.  And since it is what I consider 'best practice' and the main reason this thread exists (this structural problem) then I thought I should mention it ... decisively. :-)

 

Other fields are not an issue - they can go into a 1:1 relationship off LineItems if there are many of them or they are quite different but it would be rare and regardless, the DOLLARS should be together.  Financials, particularly AR is the most highly reviewed, adjusted and reported aspect of a business.

 

I have a table called "trans" which is a Invoice table.  This is where all of my invoice info is, this includes things like shipping, tax, employee times and etc.  In this table I have product and service totals from my product and service tables. 

 

Normally I would create a layout from product and put my invoice data in the header and footer and do a summary at the bottom to show invoice totals.  I can't do this in this file because they have 2 tables with line item detail for this one invoice. 

 

I would really like to see the fields in this 'trans' table.  Do any of these fields contain duplicate information, such as Customer Address, Ship Via, Pay Method, CustomerID?  Does Trans hold either a ProductID or ServiceID?  I think so or otherwise it could not relate to Product or Service tables.  Wouldn't that be LineItems?  Shipping and Tax and probably employee times (with charges?) should be in LineItems so an adjustment in shipping can be entered against the invoice if needed (so ALL shipping costs can be totalled in a single table) and (smaller point) if shipping is in the Invoice table then adjusting the shipping will mean losing the history of it.  If some dollars are in the Invoice (shipping and tax) and some dollars are in the LineItems and some dollars are in a Payments table for example), then pulling all dollars together becomes far more (unnecessarily) complex.

 

So I see creating a primary invoice table if there isn't one (use import to unique from Trans) to generate one of each invoice number and include any specific invoice fields then using Trans as the LineItems.  Products/Services should be a single Products table.  Researlising the Product/Service would be well worth it to have your AR dollars all together and simplify financials. And it would not be that difficult; even before FM, I performed many migrations from financials of Paradox, AccPac, Mas90 etc to database invoice/lineitems structures.  All it requires is a few temp fields to hold the new IDs and thinking it through first.

 

 The services are in the visible tab, the product is almost identical in the next tab.

 

When you see things that are 'almost identical' it is a hint that they should probably be combined.  Another major hint is when you have fields numbered such as Month1, Month2 etc.  :-)

 

If you cannot be sure that all items of both types will fit on a single page (using two portals with sliding), you will have to find some other trick for it - for example, use SQL or a dummy table to unite the line items ad hoc for the printout.

 

It would be worth fixing it properly once and forever have it right than go through this trouble.

 

Colby, if you do not simplify it now, you will be forever having to create needless calculations and summaries just to combine dollars from different tables and you will create (at minimum) double the work for yourself.  I may be wrong in my perceptions here so I would really like to see the requirements fleshed out a bit more.  

 

If you can provide empty clone or at least screen shots of your tables showing field names (only Trans, Products and Services) and Invoices (if you have one) then maybe we can help you pin it down and even guide you through the migration of it.  You are not alone.  We will help.

[comment]

I believe we had this disagreement before. The main point, I think, is right here:

 

Other fields are not an issue - they can go into a 1:1 relationship off LineItems if there are many of them or they are quite different but it would be rare and regardless, the DOLLARS should be together.

 

In case the two line items tables cannot be completely united, you would create a super-type table, thus creating three levels: the basic line-item (sub-type) level, a "dollars" (super-type) level, and an invoice level. Whereas I, being averse to 1:1 relationships, would make invoices the "dollars" level.

 

Now, I would like to say that there is no reason not make invoices your dollars level, but I can't. It's just a matter of choosing the lesser evil - which makes it a judgment call.

[LaRetta]

You are right, we've had this discussion before and it is rare I disagree.  An Invoice is a 'presentation to the customer' created to provide a dollar balance of the transaction itself along with breakdown of charges (in form of LineItems).  

 

But to the business and In the majority of cases, we pull information and report from LineItems, gathering the dollars in comparisons, in grouping their account codes for aggregate and display for general ledger, in displaying against their Products as portals and so on.  LineItems is King - not Invoices.  All Invoices does is ask LineItems for its total for display so we can tell the Customer.  And yes, I understand ageing triggers off Invoices but again ... that is the customer side.

 

1:1 rocks, Michael, it always has ... and with today's configurations and devices, narrow tables are simply necessary to keep speed up but I see no need for 1:1 table off LineItems here ... I'm just saying that if there IS a need, it is easy to implement and just as easy to work in.  There MAY be a need for 1:1 off Products depending upon the fields for Services but even that might not be needed.  I suspect what is needed is a standard relational structure with 3 tables, Invoices, LineItems and Products with ALL dollars as DETAIL lines in LineItems.  Life is then simple.  

 

To determine a structure based upon incoming migration data is not a good approach.  It is far better to manipulate the migration data to fit proper structure.  You taught me that. 

[comment]

To determine a structure based upon incoming migration data is not a good approach.  It is far better to manipulate the migration data to fit proper structure.  You taught me that. 

 

I don't recall that, but it could very well be so. In any case, I am perfectly willing to stand behind such a nicely put statement. If anything I said above seems to contradict it, it's merely a failure on my part to convey my thoughts accurately enough.

[LaRetta]

If anything I said above seems to contradict it, it's merely a failure on my part to convey my thoughts accurately enough.

 

NOBODY articulates better than you do - it is the decision to continue with split LineItems because that is how they migrated which causes my concern.  

 

We rarely get a chance to start a solution over.  It is far better to deal with structure change and re-serialising in a migration ONCE than to deal with added unnecessary complexity over and over every day and forevermore in the solution.