Jump to content

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

Recommended Posts

Posted

Hi. I'm new to the board and I've done a fair amount of searching to try to find some help, but I figure it can't hurt to just ask. I'm a newbie to FileMaker, and a novice at best when it comes to relational database design. Here's what I need to do and my understanding (or lack thereof) of how FileMaker can do it.

My company has a simple database that we use to keep track of orders placed. It is functional and good in most ways, but customer specific data (contact info, etc) is stored along with the order specific data (quantity, item, etc). So if a customer places another order, their contact info must be entered again. We would like to change this so that we have a customer database that we can use for contact management and an order database that we can link to the customer database. Ideally, we'd like to be able to create a new order, type in the last name or company name and have the customer's information be looked up and fields filled in. if the customer doesn't exist, we'd like to be able to enter customer information directly into the orders database.

I've created a new database for the contact information using the template provided by FileMaker for contact management. I've tweaked the layout a bit and linked it to my orders database via the customer ID and order ID fields.

Of course the part that i'm stuck on is how to get the lookups to work and how to get the fields filled in automatically and how to see which order IDs belong to a specific customer...

Do i need to create a portal in the orders database and fill it with the fields in the customer database? Or can I just seperate the contact info related fields in the current orders database to a seperate table in the same database? I'd like to be able to create a lot of specific queries to analyze customer ordering behavior, send letters to specific types of customers (insititutional, individual, etc)...

i know i'm asking for a great deal of help, but if it's possible for someone to just lay out the basics i'm sure i can fill in the blanks... i figure i'm going to be banging my head against the wall for a couple of weeks trying to get this done, so any help anyone can offer me will be greatly appreciated... thanks!

confused.gif

Posted

Hi andyplayit,

You should have a Customer table related to Order by Customer ID. It's a bad idea to try to relate them by Name as names change over time.

I'd recommend starting an order in the Customer table, searching for the Customer, then if it doesn't exist create a new one. Then from there, script the creation of a new Order, using the Customer ID from the found Customer.

There are other ways to do this, but this is probably the easiest. Since the parent record (Customer) is needed to complete the Order, and you don't know if the order will require the creation of a new Customer until you check if it's already in there, it makes sense to just do that at the start, rather than getting halfway through the order, and then jumping out to create a Customer (although if the workflow requires this, it could be done.)

Since an Order usually has a lot of fields (and probably Line Items,) it will be best to not use a portal for Order record creation from Customer. You can still use a portal to view and navigate to related Orders from Customer.

Posted

yes, i did plan on the link being a one-to-many link using the customerID field. one customer ID can have many related orderIDs. each orderID is realted to one and only one customerID. but the end-user is going to want to type in a last name and get a pull-down menu with matching last names that would then populate all of the customer information fields, or type in a company name and be able to scroll through the different individuals at that company and click on one and have that populate all of the customer information fields... but it may be even easier to do it the way you suggest--starting at the customer databse and scripting the creation of a new order related to the current customerID.

so assuming i could make that script happen... i'm still stuck on how to get the orders database to display information in the customer database (name, address, etc) with only the CustomerID to go on.

Posted

I'm not sure what you're stuck on. To view Customer details on the Order, just place related Customer fields in the Order layout. To view Orders from the Customer layout, use a portal to Order with Order fields in the portal.

Posted

you know i might just be able to get this to work. simpler than i thought. thanks, ender, for the help. i should have some time to try this tomorrow. i'll keep you posted.

Posted

right so i finally got a chance to work on the database today...

i've got it working--mostly.

i've created a script in the customer database to open the orders database and then call a script in the orders database which creates a new record. i'm having trouble getting the contents of the customer record's fields to import into the new record in the orders database.

as suggested, the customer information fields in the orders layout were inserted from the customers database directly. i've been trying to find a script that will insert the customer ID from the last record viewed in the customer database (and hopefully the rest of the related data) into the orders layout. i found a few that sound like they'll work, but no luck.

am i running into problems because i've got 2 seperate database files? should i just import the data and functionality of the customers database directly into the orders database and just keep them in seperate layouts?

Posted

ahh, and now i'm told i will have to purchase FM Migrator in order to combine the two databases into one... it seems like it would definitely work if i had everything in one file, but i don't think it will work across files (not within my limited knowledge at least)

Posted

You do not need to combine your files to make this work. Try using a global to remember the CustomerID from record-to-record or from file-to-file.

Remember, Since Customer and Order are related by CustomerID, you don't really need to import all your Customer fields into each Order. If it's important that the Customer info on an order is permanant (does not change if the Customer data changes,) then use Auto-Entered Lookups in copies of customer fields in the Order file. Otherwise, if changes in the Customer record should be seen in all Orders, then just place the related Customer fields in your Order layouts.

Posted

You do not need to combine your files to make this work. Try using a global to remember the CustomerID from record-to-record or from file-to-file.

I'm confused, I can't use a global for customerID, since that would force the value to be the same for all records. i need a unique customerID for each customer record.

Posted

this is getting over my head...

script the creation of a new Order, using the Customer ID from the found Customer.

this is the step that's puzzling me. i have a script set up which creates a new record in the orders database, but getting it to "use the Customer ID from the found Customer" does not seem to be doable.

Posted

Certainly doable. Using that global:

[ From Customer ]

Set Field [ Order::gCustomerID_Temp ; Customer ID ]

Go to Layout [ Order ]

New Record/Request

Set Field [ Customer ID ; gCustomerID_Temp ]

Commit Record []

There are other ways to create related records, but this is pretty straight forward.

This topic is 7315 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.