Line Item relationship problem

[Rochelle_505]

Hello all,

This should be fairly simple(Famous last words) to accomplish. I am trying to have a few fields auto populate, but I am having a problem with the table occurances/relationships. I want to be able to put in a product # on an invoice and have the following fields auto populate from the product#

Supplier#

SupplierName

GraphicsSupplier#

SAP#

ProductDescription

When I enter in the product# the ProductDescription and Supplier# are auto populating(from a lookup), but I can't get the other fields to do the same. PLEASE HELP!!I attached jpg's of my relationships and invoice table with highlighted fields.

[Søren Dyhr]

When I enter in the product# the ProductDescription and Supplier# are auto populating(from a lookup), but I can't get the other fields to do the same. PLEASE HELP!!I attached jpg's of my relationships and invoice table with highlighted fields.

Would have the same itemID from several suppliers, and does it really have to be entered in the invoice??? Are you letting the customer be quality control??? If so doesn't it make much sense to have a dedicated customers table ...since they're all one offs

:

You have to deside what data should be only referenced for printing purposes, and which are needed to establish historic facts, such as the price on a given moment.

As it is do you only have to pull fields from a realtion away to make then show up in the portal, if they not are going to be a historic fact. You don't need usntored calc as you perhaps were used to with previous versions.

--sd

[Rochelle_505]

The main reason why we need everything together is because of 2 monthly reports, one for supplier and 1 for customer, that require all this info. What do you mean by dedicated customer table?

[T-Square]

What Soren is saying is that you don't need to store all that information about the product in the invoice line item--it's redundant. The only reason you need to copy information into a new place is if you need a historical record of what something was. For example, if you store a part price in a parts table, when you create an invoice using that part, you want to transfer the price to the line item, since you're probably going to raise your prices soon anyway, and if you didn't store the active price in the invoice, you'd never be able to tell what you charged someone.

Now, as to the need to create reports with product description and supplier, you can always put the information stored in the linked tables on a report, and the report will display up-to-date information from those tables. You would only need to put this information in the line items or invoice if you needed to store the information at a given point in time--not a likely situation for a product description (granted, I could imagine needing the name of the supplier at a given time...)

Filemaker 7 allows you to place any field from any linked part of the relationships graph on a layout.

[Rochelle_505]

There is a lot of info for one line item, unfortunetly I am only trying to do what my boss tells me to. I guess that is what the person who is going to use the reports needs to see. This database is only for a small department and is not our primary invoicing system. Any suggestions on how I can get these fields to populate?

[Søren Dyhr]

I guess that is what the person who is going to use the reports needs to see. This database is only for a small department and is not our primary invoicing system. Any suggestions on how I can get these fields to populate?

We both gave the answer, a lot of these gets pulled over the relations, when the report is generated. There is no need to "populate" ...if they are related information to the itemline. This is not being obstinate to you boss - he'll get his info just in time. Only fluctating prices needs to get stored and perhaps a key not the actual data from the salesman in charge of the order.

By this concept can a report even pull the number of illegitimate children the salesman have to feed ...if needed. But it should only be his ID that might be stored in the itemline.

--sd

[Rochelle_505]

Thanks for the info, I will just try to do what I can.

[LaRetta]

Hi Rochelle,

I'm afraid I can't read your relationship graph to tell your structure so it's difficult to give more specific advice. But if all the fields you want to access in your report are related, you can place those related fields directly in the report - no need to copy the data again.

Generate the report from whichever table includes the records you want as the body. But vs. 7 allows placing related fields in leading parts, etc. Play with that and see what happens. If you still get stuck, post your files and we'll help you further.

LaRetta

[Rochelle_505]

Hi Laretta,

I have tried to attach a clone(w/o records) of my db, but it is still too big. If you click on the attachments in my first post they will enlarge for you. Thanks so much.

[Fenton]

Rochelle, please collapse your table occurrences to only show the keys being used, then arrange them nicely. It is very difficult to read a graph otherwise, for us or for you. Use the little square box on the title bar of the TO. It has 3 states. Just click to experiment and see.

Basically your problem is that you have the Supplier TO attached to the Invoice itself, not to the Product TO. If each Product has its own Supplier that is where it should be; not the Invoice as a whole. Because you have possibly several different suppliers in one Invoice, per Product, which is per Line Item.

I see that you have Invoice TO to Supplier TO, then attached to Line Items, on the other side of it. But this won't work. Suppliers goes off of Products, as that is when/how/where a product has a supplier.

In a large file system you'd probably want these things as separate "table occurrence groups", TOGs. But yours doesn't appear that complex as yet.

[Rochelle_505]

Thanks Fenton, from a beginner to a pro like you "I'm not worthy". I am anxious to see your invoice system, any eta on that DB?

[LaRetta]

"If you click on the attachments in my first post they will enlarge for you."

Yes, I understand that. As I said, still too small. But with Fenton helping you, you don't need me anyway. :wink2:

[Fenton]

Rochelle, if you can't figure out what I was saying, which is quite possible, why don't you upload a clone of your file, if it is not too large.

It is pretty easy however to attach the lines in the Relationship graph correctly. Just delete the line from Suppliers to Invoice, and reattach it to Products.

Supplier# in the main Line Items table occurrence would be a lookup, from Suppliers. The relationship* would pass "through" the Products table (automatically). This is assuming you only have 1 supplier for any particular product. Otherwise you need a join table, and a way to either get the current supplier for that product (latest entry in the join table? manually entered flag field?), or a way to choose which (for each line item, awkward).

So there's a question: "Do you ever switch suppliers for a particular product?" A "yes" answer requires a more complex graph, but is still doable.

*I don't know if there's a good descriptive adjective for relationships that pass through multiple table occurrences:

"transverse"? To FileMaker it doesn't matter, but it is hard to describe in English.

[T-Square]

Fenton--

A quick workaround for the multiple supplier problem would be to maintain a separate Product entry for each Product/Supplier pair. Although this duplicates Product data, it would seem to me the benefit of simplicity would be great. This assumes that the product data remain moderately stable, since maintaining changes in duplicate data is untenable. It also assumes that the number of suppliers is not great.

A secondary benefit (beyond simplicity) is that supplier variations can be easily designated in their Product descriptions.

To distinguish the multiple product entries in data entry, I would add the supplier code, either by calculation or literally in the Product description.

[Rochelle_505]

I did make some progress, now when I put in the product # in the portal all of the fields auto-enter. The problem now is that 3 fields the Supplier Name, Graphics Supplier #, and SAP # are auto-entering the first record from the supplier table for every entry.I followed Fenton's advice on the Supplier relationship to the Product table. Any ideas would be greatly appreciated.Thanks everyone!!

[Fenton]

If the relationship from Line Items to Products is based on Product ID, then from Products to Suppliers is based on Supplier ID, then products with the same supplier in Line Items will show the same supplier data. But products from different suppliers should show different.

Is there only 1 Graphics Supplier and 1 SAP per Supplier? That's the way you've got it set up, since they are just fields in Suppliers.

Otherwise I don't know what's going on. You need to post your Relationship Graph again (cleaned up). Or post a clone or sample of the file.

It is also possible that you are specifying the wrong table occurrence when you put Graphics Supplier and SAP in the portal. This shows in portals as the problem you mentioned, all the same as the 1st. But since they should just be related fields from the Suppliers table occurrence, I don't see how that would happen.

They should not really be looked up INTO fields in Line Items, unless they change often between suppliers and you need a historical record.

[Rochelle_505]

:woohoo:

I tried so many different ways to link my tables, I was starting to pull out my hair. I was finally able to have the supplier and product tables combined into one (via ODBC) and all the fields are auto entering exactly the way I wanted them to. Thank you all for your advice and expertise!!!