Jump to content

Is this a join file question?


Alex T.

This topic is 7553 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. crazy.gif

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

Link to comment
Share on other sites

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 mad.gif

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. smile.gif

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.' smile.gif Otherwise, Alex could be given incorrect advice. smile.gif 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. crazy.gif 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). smile.gif 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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!

Link to comment
Share on other sites

  • Newbies

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 :-(

Link to comment
Share on other sites

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 blush.gif

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7553 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.