Is this a join file question?

[Alex T.]

Hi, I'm relatively new to filemaker, and any help in this matter would be greatly appreciated.

My databases are:

• Sales (Imported from a text file, basically a glorified invoice)
  
• Orders (this is orders to suppliers, the product codes are read from the sales database)
  
• Products (There are more than one of these databases, one per supplier)
  

I am trying to work out how to make the Sales database read in descriptions and pricing information from different product databases depending on the supplier. Each product has a 3 letter code identifying the supplier, and all codes are unique, but I can't define a relationship from this field to more than one database.

I have read on the forums about using a join file to accomplish this, but I can't make it work for the life of me :-(

Can anyone help me?

[Ugo DI LUCA]

Well, your settings aren't that standards....

Generally, it would be something like

Customers (CustomerID)

Suppliers (SupplierID)

Products (Product ID- SupplierID)

Orders (OrderID - CustomerID)

Invoices (InvoiceID - CustomerID)

Line Item (ProductID-CustomerID-(SupplierID)-OrderID-InvoiceID

Then, it would be easy to catch the related Products of a Given Supplier, as well as compiling any order or invoice using the Line Item, which is the crossroad of all the other files.

Just some thoughts.

[LaRetta]

Hi Alex,

Products (There are more than one of these databases, one per supplier) ... and ... I am trying to work out how to make the Sales database read in descriptions and pricing information from different product databases depending on the supplier.

Ahhh, the crux! No, you can't relate on the same KeyID to more than one database in the way you're wishing.

Your Products should all be in one database. Then, when viewing the data from the Products db, Products would be the left (Main) db, joined to Sales on the ProductID and it would display one Product per record. Each Product record would/could have many Sales (known as a one-to-many or 1:n). Sales for each product record could be displayed in a portal and show you all sales for a given product.

While in your Sales db, Sales will become your Left (Main) db, joined to Products on ProductID. This view would be used as a

[Ugo DI LUCA]

LaRetta said:

You can name your dbs anything you wish but it could confuse people here who may be assisting you in the future. What you call Sales is normally called Orders.

Aahh, it's been some time since I wanted to ask, because of my being a foreigner

Would a Product file be called Product or Inventory ?

Would a Purchase Order be called Purchase Order or Order or ?

Would a Customer Order be called Customer Order or Order or ?

Would a Quotation.db be called Quotes or Proposal ?

Would a Payment db be called Payments or Transactions ?

Just an aparte, but since I've seen many errors and also made some....

Back to the topic.

Even if you could make it relate to 3 or more databases, using a combination of lookups r/and calcs, it won't be interresting and functional at the end.

Thanks.

[Alex T.]

Hi LaRetta, Thanks for your advice in this matter.

I agree that it would be simpler to use one single product database, but I was really hoping to use one per supplier. Is this possible in this case, or should I just resign myself to using one?

TIA

[Ugo DI LUCA]

Alex,

The problem with this kind of structure is that it can't "naturally" evolve.

Say you will contract with a new supplier. You'd need to rebuild a new deb, reassign relationships, etc.

Otherwise, as I said, you could have 3 lookup fields, each being filled using the 3 relationships in place to the 3 Products db, and an ending calculation

c_ProductLookup = Case(not Is Empty(lookup1), Lookup1, Case(not IsEmpty(lookup2), lookup2,Case(not IsEmpty(lookup3), lookup3,"")))

The kind of structure you're describing won't help in constructing an inventory, if your job does require one, and would be considered, IMO, as tedious as working with repeating fields...

HTH

[LaRetta]

Hi Ugo,

The naming really isn't that important as long as YOU know what they are. However, I think most on these forums think of ORDERS as being what is sold to Customers, so if Alex discusses his Orders db, it may confuse some people.

To me, Inventory and Products are two separate dbs. Products contains ProductID, Description, UPC, Price (although this may be a separate db also which contains Retail, Wholesale, Cost, etc.), Picture, etc. Inventory would contain the LineItems of quantity in/out (including adjustments for physical inventory), etc. Orders would contain a 'promise' from a Customer which may or may not be followed up on. Invoices would contain the bill for products/services delivered. PurchaseOrders would contain a promise to Supplier to purchase and would become a Payable when the promise was fulfilled.

Each business refers to these things a bit differently. I think that's what a Developer should go by. I just think it's important that, if referring to Orders, that people be made aware that it means 'orders to suppliers' as opposed to 'orders from customers.' Otherwise, Alex could be given incorrect advice. Same with Purchase Orders -- some enter a PO from a Customer (to list all products and quanties) and it's the same as an order from a Customer. Some enter PO's for products ordered from suppliers. So, clarification is the key.

Hi Alex,

There have been many discussions about 'how to combine several dbs' so that they can relate as 'one db' within structures. I really haven't seen a functional solution. You would need one db in which, whenever you create ANY record in any of your Product dbs, writes a new record to it. And then relate Sales to THAT db. But it means that you'll be having to pull grandchildren data and you won't be able to get a complete inventory report, etc. ... well, that can get quite complex.

I'm not saying it's not possible. What I know is only a nano-fraction of the FM universe (there are more things in heaven and earth than what I know). Others may have their own opinions and ideas. I would have one db for simplicity. Can you say why you want them separate? Maybe we could address that?

LaRetta

[Ugo DI LUCA]

Thanks for this clarification...

Another one please.

What's it called when it concerns goods you're receiving and goods you're delivering ? Shipment and Deliveries.

But when bringing this in terms of FileName , what would be called the File:

- I'm using to "carry" back the items I purchased ? Transportation Order ?

- I"m using to "check" the good I received ? Shipment ?

- I'm using prior to invoicing my customers, with the list of good delivered ? Delivery ?

Again, this is just to make sure, as you pointed out, that I'd correctly answer a querry, or that I'd be understood when asking.

Thanks.

[Kurt Knippel]

Ugo,

Are you trying to unravel the intracies (or mysteries) of the English language? Good luck, I been speaking it my whole life (started college as an English major) and I cannot figure out half of it.

Many times the terminology is dependant upon the industry that the DBs will be used in. Also, as I point out in another thread, the naming that is used does not necessarily denote a different table. For instance a Purchase Order may not actually be different than

a Customer Order ; Shipment is not necessarily different than Delivery (in fact they may even be combined as Inventory).

[Ugo DI LUCA]

Kurt,

Even with +2000 posts, I'm still looking for help and as this Forum has become my online help, I'd just like to make sure I'm using some conventional terminology to define such or such....

You've not finished with my querries guys...

[Kurt Knippel]

Then in general the following terms get used (all may appear with or without the .fp5 extension):

Main or MainMenu

Customer - defination depends upon the specific business or industry. Many people do not use this, opting for the Company/Contact paradigm instead.

Account or Company - records of businesses or companies

Contact - records of individual people

Invoice or Order - these are often used to mean the same thing

LineItem - just about any join file, most often used to define the join between Invoices and Products

Product - records of products that are sold

[ESpringer]

WHY have you thought it's more convenient to use separate product databases for each supplier (besides, perhaps, the one-time nuissance of merging them)? I'm wondering whether you're unaware of something about how different finds, sorts, layouts and scripts within the same database can accomplish whatever it is that you think you want multiple databases for...

Having one database for products, each of which points through a relation to one of the suppliers (information on which is in one suppliers database) would have many advantages -- such as the ability to do new things (layouts, scripts, etc.) in that database and have it apply across the board to all suppliers right away. If different suppliers want different kinds of things, you'll still be able to do it with some ingenuity.

So... If there's something you're wary of using one consolidated database to do, this forum can probably help you work it out!

-ESpringer

[jjosh12]

Ugo,

The term you are thinking of (shipment and delivery) is commonly called "Shipping and Receiving". (I hope you were serious when you were asking about this, your english is a lot better than mine and this is the only language I know)

[RWX]

As LaRetta said, your problem is the multiple Products databases. A file cannot be related to more than one file via the same key. In other words, relating Sales to Product A database prevents you from using the same field in Sales for the relationship to Product B database.

Why is it so important to keep your product databases separate? Why not simply merge them and, if necessary, create a different layout for each product type or something like that? In theory I suppose you could use a join file to link your Sales database to different Product databases. This seems like a lot of work and so far I can't see any real incentive to do so.

-Rob

[Alex T.]

Why is it so important to keep your product databases separate? Why not simply merge them and, if necessary, create a different layout for each product type or something like that? In theory I suppose you could use a join file to link your Sales database to different Product databases. This seems like a lot of work and so far I can't see any real incentive to do so.

Thank you everyone for your help :-)

The idea was to keep all the different supplier's products seperate so that each file could have a different range of options available, specific to that range of products.

I agree that a single products database is the way to go, but what about the options? I'm new to all this, but I'm figuring it out slowly. Thanks again everyone for all your advice!

[Alex T.]

I'll expand :-)

Each product in the database may (or may not) have various options applied to it. For example, a piece of jewellery may have options which affect the price, such as metal type, stone type etc. Some different products will have options which don't affect the price, such as colour.

I have been trying to implement this with conditional value lists, but I fear it is over my head at this stage :-(

[Ugo DI LUCA]

Alex,

May be this was a Join File Question, but not the one you expected...

If these options only are "components", and you actually also need to keep track of how many of each component is used in the finished product, you may use an external Component/Option file and a Join ComponentsInProducts.

All Components and quantities would therefore be affiliated to the Product File, and you'd be viewing the options from a portal in the Product File, which would remain empty if no option were selected.

If not, keep the Component/Option file and affiliate Components to Products involving a one to many relationship from the Product to the Component file.

The options still would be viewed through a portal.

If you're not happy with having a layout with an empty portal, just use a layout with a button "Option List" that would disappear from layout if no related records in the option file has been detected. This tip is known as Visibility, and you'll find plenty of examples here on the Forum.

It would even be easier to calculate the price given some data in the related Component File.

So now definitely, forget about separate Product Files, there's no need to get you in a total mess...

Josh, thanks for the clarification and

[RWX]

Alex,

I would suggest simply setting up a different layout for each product supplier. Certain fields will be common to all products (ie: Product Name), while others might be supplier-specific. By setting up different layouts, you can make just the required fields available. Essentially you will combine all of the fields from each of your three databases into one single database. If Supplier A doesn't require the Weight field, for example, omit the Weight field from Supplier A's layout. The field will still be part of the record, but it will contain no data.

Hope that helps!

-Rob

[StephenG57]

I have somewhat of the same situation I'm struggling with at work.

I am trying to implement an Inventory database for an optical shop. Because we deal with many different products (frames, blank lenses, contacts, pharmaceuticals, misc. parts), all with different variables (ie: frames [male/female, material, color, size, etc.], blank lens [size, material, power, base curve, etc.], I thought of making separate databases for each category of product. But, after trying to link all of these together in a join file, I've come to the conclusion that I would just be better off with one (somewhat massive) PRODUCTS database containing all the different variables that could join with my PO and Invoice files through a line item file. While the PRODUCTS database will get somewhat "crowded" with records, I have found no way to have these separate while still maintaing PO and Invoice relationships (and my sanity).

Any thoughts?

[ESpringer]

StephenG57,

FileMaker shouldn't have any problem with PRODUCTS file being "crowded" with records -- that's what it's so good at! The problem comes with keeping track of many fields, which can make navigating layouts unwieldy.

So you might want to think about which of the variables really need entirely separate fields. I'd include one (or more) repeating field in which misc "specs" like "finish:matte" can be dumped... so that if you suddenly start carrying an item with a whole new set of parameters, you don't have to add new fields. Repeating fields are good for searches, since the search will find records with your search string in ANY of the repeating slots.

Also, you might want to create a calculation field that concatenates all of the specifications (granted most will be blank ones for most items), so that there's a compact description available. Then you can also find products by typing a relevant string in that concat field.

[John Caballero]

You could create an Options table that is related to the Products table by category ID. Then you could create values lists based on the relationship.

[StephenG57]

After going ahead and working with one Products database, I basically came to the same conclusion. While there are multiple fields for multiple products that I find necessary to differentiate, many of the miscellaneous products will suffice with a concatenated specifications field. Thanks so much for the suggestion.

[StephenG57]

I have set up a field called "prod_category" in which I can categorize all of the different products (ie: frames, lenses, contacts...)so data entry can be made in different layouts displaying category specific variables. I then set up value lists based on different relationships such as you suggested...it works great. Thanks.