Relationships for a beginner

[mvhurlburt]

Ok I know this is probably very simple to do but I still can figure out how to make it work. In a nutshell I have been working on a database to manage leads, sales, commissions, assets, estimates, work orders, etc. This question is really applicable to relating alot of these tables and layouts but simply put what I am attempting to do is this. In my lead management layout we store contact info including name, address, phone, email, etc. As well as some of the estimate details, lead outcome, sale price, marketing info etc. All of that data is in its own table; "Contact Management". The commission layout has its own table; "Commission". This layout is used to calculate sales reps commissions and generates a report. What I am looking to do is use various data from the "Contact Management" table, after the lead info is entered and if a sales is made a "Generate Commission" button is pressed. It switches the layout and creates a new record. In that new record in the "Commission" table/layout I would like to import data from the above lead such as Name, Address, Contract Date, Sales Rep, Sale Price, Customer #, etc. essentially linking a commission report with a given sale.

So the question is:

1) Is the best way to do this via relationships in order to display and manipulate data from "Contact Management" in the "Commission" table and layout and how do I go about doing that?

2)In order to in fact link these and then view them at a later date my above approach of click a button, switch layout & create a record is obviously flawed in the sense that when I pull up that lead after the commission has been created and attempt to view it again by pressing that button it will ultimately create a new record. What is the best way to link the two layout?

Sorry if this seems fairly trivial but I can't seem to figure it out. Any replies will be greatly appreciated.

Thanks,

Mike

[bruceR]

Post your file then we'll discuss it. Your questions are too general. No, this is NOT "very simple". It is a moderately complex multi-table design and we do not have enough information from you. It does seem obvious that if your script creates a new record, then calling that script again will create a new record again rather than just navigate to the layout. It is therefore hard to figure out what assumptions you could be making that would lead you to ask the question.

[mvhurlburt]

Let me just phrase this a bit more simply because there are alot of other layouts.

I have two layouts and two tables; layout 1 & table 1 as well as layout 2 and table 2.

Table 1 has one number field; Cost. Layout 1 contains an input field for cost and a button. When the button is pressed I want to make the following happen.

Layout 2 appears, a new record is created. Layout 2 displays the following text; "The cost from table 1 is: (Cost From table 1)" as well as 1 editable field that also displays the data from (Table 1 -> Cost).

Thats what I would like to know how to do in a nutshell....the script question I'll work around, I think I know how I will handle that issue.

In other words the essential question that the above scenario presents is the following: How can one enter data in a field in layout + Table 1 and have access to that data in layout + table 2.

If someone can help me with that I should be able to apply it to the original scenario and accomplish what I'm looking to do.

Thanks,

Mike

[mvhurlburt]

Here is a file attempting to accomplish the above scenario....

TestFile.fp7.zip

[bruceR]

Well, I don't think that's really the question. At this point - sorry - you seem to know so little about FileMaker that I don't think you're really prepared to build what you have in mind.

Your question doesn't make any sense. Why would you want to do this? My question is not rhetorical. My question is an attempt to ask you explain what you're trying to accomplish; and to get you to explain more about where you're coming from. Part of the reason for asking these questions is that there are a LOT of potential answers. For instance - why do this at all? Why not just DISPLAY the value from table 1 in layout 2. Does layout 2 really need to have a different table? If it does, do you know how to make a relationship and display data from table 1? Do you know how to pass a value in a script paramter? What is the functional purpose of these two layouts? Why don't you stay in layout 1 and show the information from table 2?

I suggest you look at LOTS of example files, including the templates that come with FileMaker; and complete the tutorial that comes with FileMaker.

[LaRetta]

I don't believe someone should be turned away just because they are beginning in this business (using FileMaker).

In my lead management layout we store contact info including name, address, phone, email, etc. As well as some of the estimate details, lead outcome, sale price, marketing info etc. All of that data is in its own table; "Contact Management".

You need Contacts and Estimates to be split into two separate tables because one contact might end up with two estimates, correct?

In that new record in the "Commission" table/layout I would like to import data from the above lead such as Name, Address, Contract Date, Sales Rep, Sale Price, Customer #, etc. essentially linking a commission report with a given sale.

I will be linking to a file which you should study and understand. It has what I call 'the big four' pieces of a solution, ie, Contacts, Invoices, LineItems and Products. You will apply this file's principles to your situation. Your Contacts would be name, address, phone etc. Your Estimates would hold your estimate details. Each table should begin (first field) with an auto-enter FM-generated, unique, meaningless serial ID.

Unzip this file now: Basic Invoicing Demo by Comment ... and then open it as I describe how it relates.

What is the best way to link the two layout?

You do not link layouts - you link tables by their IDs. Open File > Manage > Database and go to the Relationships tab. See how the IDs are joined? One Contact can have many Invoices so you put a ContactID in your Invoices table. Now Contact information is available to Invoices. On the Invoices layout, click the CustomerID field. It has a value list attached so User can select the right customer to assign to a new invoice.

After you select the CustomerID, all customer (contact) information can now be displayed in Invoices. The same theory is repeated, ie, one invoice can have many lineitems on it so the InvoiceID is put into LineItems. That portal on the invoice is based upon the lineitems table. You can add items there by click into the ProductID field. The 'cost' is then looked up from the Products table.

If you study how these tables relate, you will get over this beginning hump of confusion. Ask questions; don't be discouraged. We will help you understand it.

[LaRetta]

By the way, this is tightly tied to: your prior post where Comment provided a demo.

Once you understand the basic principle of relating tables so information will flow between then, his file will make a lot more sense. But pull it into your study pile - it is a basic, easy-to-understand demo as well.

[Fenton]

mvhurburt,

Basically, the facts we've been given are open to interpretation (at the least). Starting from, as Bruce says (paraphrased), "what are you really trying to do?", in plain English.

It is quite easy to display an editable field from one table in the layout of another RELATED table. But, the question is "how related?". The reason to edit such a field from elsewhere; it would almost always be a "child" to "parent" (or some one-to-one), and you'd have the understanding that you'd be changing the value in another table.

Your file's relationship is based on the 2 cost fields. Which could be a techique to hide the other, if either is edited (an obscure technique for sure). So, your relationship is no good, and you do not have any other fields, nor a way for us to even guess.

There's not enough info to say what to do. It's likely a simple thing to do, but there's several (very different) logical possibilities.

[Vaughan]

... and starting off a clarifying post with "I have two layouts and two tables; layout 1 & table 1 as well as layout 2 and table 2" does not really help: we end up with the Abbott & Costello baseball "Who's on first" gag.

All Together: Third base!

[bruceR]

I don't believe someone should be turned away just because they are beginning in this business (using FileMaker).

A non sequiter. A statement with no logic.

Nobody turned anybody away. What I *did* suggest is that the OP give us MUCH more information about where he's coming from; and be appropriately cautious about attempting to do something he is not prepared to do; and use readily available resources to inform himself.

[LaRetta]

A non sequiter. A statement with no logic.

The OP has a name. His name is Mike. What he was attempting was clear in his opening post; simply he didn't know how to use a relationship to look up the cost (which is why I gave him Comment's invoice sample which shows looking up the price from Products).

and use readily available resources to inform himself.

I have a surprise for you ... WE are one of those resources. If you don't want to assist someone who is new, don't respond. There are others here who are willing to take a bit of time without suggesting someone simply go away until they know more.

I took the time to read his prior posts. Did you? Bruce, if you wish to attack me again, at least spell non sequitur properly. Your demeaning me will NOT stop me from posting on this forum.

[bruceR]

All right; your statement was a non sequitur; it did not follow. It was illogical. Your latest remark is more of the same. That is, you suggest that by listing some available resources, I am suggesting that he not ask questions here. You also suggest that by responding, I am expressing some desire not to respond. It is all really quite remarkable.

I'm not attacking anybody, I'm trying to be helpful and go several extra steps in looking at the problem and the confusion and mismatch. It is precisely because I have read the question and thought about it carefully that I responded. The original question was confusing in several ways, as clearly observed by others who have responded.

I have not asked you to stop commenting. Unlike you, I have NOT suggested that people asking questions ignore the advice of experienced people who are responding. We've been through this before; I'm doing what I think is helpful based on my experience. We apparently have quite different visions of reality. I'm comfortable with my vision, and with what I try to accomplish here.

[LaRetta]

I responded to MIKE in an attempt to help him. YOU responded to ME with rudeness. You have posted behind me for quite some time ... always demeaning; always rude; posting to ME and not to assist others on the thread.

Please stop responding to ME and do as you say you want to do ... help others here (just as I have been trying to do) by responding to THEM. You and I do NOT need to like each other (and thank God because we certainly don't) but we can share this forum. I do not need to be attacked by you every time I try to help someone simply because I might see a different perspective than you. You go live in your own reality and remain a legend in your own mind and I'll continue to offer suggestions here if I think it will help someone.

[bruceR]

I responded to MIKE in an attempt to help him. YOU responded to ME with rudeness. You have posted behind me for quite some time ... always demeaning; always rude; posting to ME and not to assist others on the thread.

Please stop responding to ME and do as you say you want to do ... help others here (just as I have been trying to do) by responding to THEM. You and I do NOT need to like each other (and thank God because we certainly don't) but we can share this forum. I do not need to be attacked by you every time I try to help someone simply because I might see a different perspective than you. You go live in your own reality and remain a legend in your own mind and I'll continue to offer suggestions here if I think it will help someone.

Well, once again you have it backwards.

What is the history here?

I make a comment.

You state: "I don't believe someone should be turned away just because they are beginning in this business (using FileMaker)."

This is your usual bizarre, hostile, sad misrepresentation of something I've said.

YOU stop the accusations; and I'll stop responding to them. I will try stop anyway; but please accurately take responsibility for your part in this and look very carefully at the sequence of events.

[mvhurlburt]

Thank you LaRetta I was able to implement what I needed to based off of your file. I'm surprised however that you were the only one who understood my question. I am now able to view data from table 1 in layout/table 2 as well as use it, the only problem I'm having is those text and entry fields don't show up in preview nor for printing?

BruceR point taken....However in my opinion this is a resource that one might consult to inform himself. I find suggested reading more than helpful and sample files to be above and beyond that. I do attempt to inform myself on my own first but when the documentation can in some cases be fairly unorganized, esoteric and confusing it has in the past behooved to ask. I thought the scenario was presented in a fairly simple manner. From what I understand from LaRettas sample(I may be wrong however) is that the "=" relationship is an evaluation rather than a statement. I would like to thank everybody for their replies so far and in the future. LaRetta I appreciate that you relate to people in a personal manner and seem to go above and beyond the average poster. I appreciate being welcomed as a beginner on this board, not being viewed as out of place and consequently ostracized. While I am a beginner with regard to this particular skill set I do have experience in thinking and articulating logically....namely C, Objc-C, Java, Visual/Real Basic and more. I do apologize if my question was perceived as being vague though I do feel it was presented logically.

Thanks.

Mike

[LaRetta]

Hi Mike,

The file was created by Comment (not me) but I am glad it was helpful. The greatest resource on this forum (and there are many) can be found on Comment's blog. If you refer to those demos when you need that functionality, you'll not go wrong.

As for your printing issue, do you mean Comment's Print Invoice layout? It works for me so I'm unsure why it doesn't show up in preview. Reports with sub-summaries must be sorted in same order as their leading part (in this case by InvoiceID). Look at Layouts > Part Setup. If not sorted by this leading part, the report will be blank. Is that what you are experiencing? If not, can you attach a screen shot taken while in layout mode?

[mvhurlburt]

Hmmm I'm not sure that I fully understand the part setup so that may be the source of the problem....I tried however to remedy by analyzing Comments file to no avail. I've attached 3 screenshots, one in layout, one in browse and one in preview. I can attach the actual file if you would like. It is largely based on the contact management layout provided with FMP only with a number of modifications tailored to managing leads in the window business. I've also added onto it to accommodate other needs. I guess I'll just attach that too but keep in mind what is there right now is a work in progress thus it is a little rough. Thanks again.

LeadTracking.fp7.zip

[Fenton]

We need the Full Access account & password to open the file. We also need to know where exactly to look at what; fields, buttons (if required for what you want to do), etc..