Relational Layout problem - newbie

[MouseInAWheel]

I have mined these forums anonymously for a treasure of help so far in overcoming many obstacles in learning FMP and I wish to start my first ever post by thanking everyone very much for all the help given.

But I am now stumped and can't seem to find the solution. My problem:

I have 3 databases of line items (Expenses, Time Entries, Auto Logs) where different reps import their data from Palm Pilots. I need to create a Invoice DB from which to print a single invoice that brings in line items from all 3, and sorts using either Date, Client Code, or Project Code (common fields that exist in every DB) or a combination thereof.

I have been successful at creating an invoice layout within each DB that I can control and print separately, but I can't comprehend how to make it relational and make it one invoice intead of 3.

How do I mix in related charges from the other databases?

Are Portals the answer? If so, can you print portals across multiple pages?

Thanks,

John

[stanley]

John:

It sounds to me like you want to create a new file, called "Invoices." In that file, you'd have some data that is specific to the invoice itself (date, serial number, invoice number, etc.) and then the links to the other three files. I am guessing that you may have multiple entries from these files going into a single invoice, so really you'd want to take the serial number from "Invoices" and use it in each of the other files (in a "Related Invoice" field), and that's how you could make your relationship work. Then, when creating an invoice, you could go to the other three files (via a portal, a script, however you want to do it) and select what will go in the invoice - the items you select get the invoice's serial # in the "Related Invoice" field, and will now appear in portals (or however you want to do it) in your Invoice file.

However, portals are awful for printing. Really, you should print from the file that has the data. In that case, if data from each of these three files can occupy a line-item in your invoice (and they are mingled together) you may need to create a new matching record (in Invoices, or in another file) to take better control over your printing...

-Stanley

[MouseInAWheel]

Stanely,

I assumed it might be the case that printing from the file with the data/records/line items would have to be the way.

Unfortunately, that is not a practical option as I only want the reps to have access to those 3 files and only for importing. And I need to merge the items into the one invoice.

I also assumed that Portals would not really be the answer, so thanks for confirming that.

If I understand correctly, you suggest moving data/line items from each of the 3 files into an "Invoices" file where I can employ an invoice form that I already have used in each of the files.

So now I ask...Is there an easy or standard method to create "matching" records between 2 files? It sure would be nice if there were a magic "cut & paste" for an entire record.

I'll search around here in the meantime...Thanks for the quick reply.

John

[stanley]

John:

Unfortunately there's not a simple cut&paste for a record. However, it can be pretty simple. I don't imagine that you need all the data from your original records in your invoices, just things like Date, Description, Sales Rep, Price, Tax and what-have-you. Those are the only things you'd need to copy over. Also, because this would all be scripted, you can keep your reps away from the Invoices db fairly easily. However, I don't mean to imply that you'd use the invoice form you've already created - you'd have to create a new form in the Invoices db. Unless, of course, you don't mind having a 3-page invoice with the data from the 3 files on different pages...

The positive to this method is that you'd have a clear database of what has been invoiced and your invoice could all be on one page (if it fits).

Keep on posting if you have more questions.

-Stanley

[MouseInAWheel]

Stanley,

I think I can easily the Invoice form I have already been using, since I moved it between the 3 files originally. No problem there...

Just wondering if there was a canned routine or script, or a preferred method of moving records between files.

Although I think I can figure it out, it would be nice to see if someone has a more elegant way of doing it than what I might come up with.

John

[Ender]

You can import the different line items records into one file; just make a script that imports from each file and sets the Invoice# using a loop or Replace.

Just realize that by making a table of duplicate line items, you are going against relational design principles (not that that

[MouseInAWheel]

Ender,

thanks for the reply...

I think I need to keep the import files separate since there are about 20 fields for each type (Expenses, Time Entries, Auto Logs) that would have to be remapped to import to the right fields in a single FileMaker file. It seems like a hassle to go through that every time for different people and different line item types.

It was my original concept to have the 3 files with 20 fields and a fourth Invoice file with just 10 fields. Each individual Invoice field would then have 3 conditional and relational matches (one each from each of the 3 line item files) that contain the necessary Invoice info. (Did I explain that well enough?)

To bring into a single file of 60 fields and creating 10 conditional fields is possible. But then I'm back to reordering fields when importing and with 60 of them, it's a pain. It just seems that it should be easier than that.

Question though: Is there a way to save the matching order of fields when importing from a tab-delimited text file??

If I could specify 3 different such orders and save them somehow, I could set up scripts for importing each type of line item from different text files and bring them all into one big file.

John

[dbruggmann]

Another idea: Why don't you put all line items in one database "LineItems" from the beginning?

Now you have to maintain three different databases, which data you have to import in a fourth file to report on. But as you said, there are a number of fields which are common for all three types of line items. All you need is a new field "LineItemType" with a value list attached ("Expense", "Time Entry", "Auto Log") to differentiate between the three groups in "LineItems".

Another question: What kind are the relationships between your Invoices and LineItems? Does every Line Item belong to one and only one Invoice? (=One-to-Many) Or can one specific Time Entry or Expense belong to more than one Invoice? (=Many-to-Many) If it's a One-to-Many situation, the reporting would be quite easy in "LineItems", otherwise you would need a join file between "Invoices" and "LineItems".