Please help! Relationship issue with script printing!

[Jesse Stolow]

Hey everyone,

Sorry this is so long. I am a newbie and don't know how to explain it any better.

Essentially, the main table I created is used to create and print quotes for a potential client (before they become a real client and get assigned a Purchase Order number). When I create a new record in the Main layout for a quote, I am able to select a client from a drop down menu that is tied to the Client table (which holds all client info). The two tables are linked by the clients name.

I have also set up a third layout entitled Memo that is the printable layout for the quote. I created a script button on the Main layout to take me to the related record in the Memo layout and print the record. When I click the print button for a new client, the record prints the client name and the quote information on the Memo layout just fine. However, if the client's quote/record is for a client we have had in the computer before with a previous quote/record, the print script gets all screwy.

Instead, the Memo layout shows the original record for that client, not the new record that I want to print.

I think the problem is that I have related the Client and and Main tables together via Client Name. But Considering that I am simply using a drop down menu to pull up the client information on the Main layout, I do not know how to relate the tables via a number.

Thanks!

[aldipalo]

In Client file set up a field "ClientID>Number." Then click "Options" and "Auto Enter" and check "Serial Number." This creates a new and unique ID number for each New Client.

Of course, if you have already have added clients to your database you will need to create an ID number for them as well. You can do this by creating a script to "Loop" thru all your files and add a sequential number.

HTH

Then add a similar field to your Child Table and relate the two tables by the Client_ID field.

Generally, it is a lot easier and more accurate to relate tables by an ID field that is unique then a text field that can be similar.

[Jesse Stolow]

I must be doing something wrong here because I tried what you said and it is still not working properly at all.

Edited August 21, 2006 by Guest

[aldipalo]

Can you post a copy of the file?

[Jesse Stolow]

this forum will not allow me to attach copy of the fmp file.

[Lee Smith]

Files have to be compacted. Use the operating system's Create Archive of [color:blue]"YourFileName", and then select that in the [color:orange]Manage files option here.

For additional information on this proceedure, Read the FAQ in the [color:blue]Help Menu above

Lee

Edited August 21, 2006 by Guest

[Jesse Stolow]

There is no pasword. This file has no records in it for ease of use. However I have another identical file with a few records in it that I was using to test.

Total_Window.fp7.zip

[aldipalo]

I looked at your file can you give us a better understanding of what you are trying to accomplish?

[Jesse Stolow]

This file will enable my company to write, print and keep record of quotes, contracts, and orders all on the computer (we are currently using the typewriter).

I work for a company called Total Window that makes 100% fully custom window treatments (totalwindow.com). No two orders are alike.

My problem is that the way I created the relationships and the Main table, when I select a Client from the drop down list for a new record that has a previous history of doing business with us I can't get my Script button marked Print Memo to print that specific order. Instead, it prints the first order from that client.

I have the same problem with printing Order forms to manufacturers since I set that relationship up the same way as above. That situation is worse here because we use a handful of manufacturers to make all of our jobs fr our clients.

Aside from that ,everythign else works great. I am just so stuck because I know the problem is most likely in the relationship being linked by names, but since I only use drop down menus to recall the information, I do not know any other way to relate the information.

I am available by phone at 561-236-3069 as well.

Thanks!!!

[bruceR]

You're not stuck. This is an easy to solve problem, though you may not yet be aware of the techniques required to solve it.

Your problem can be fixed and you can link by ID instead. One way to do this is to create a second relation based on ID and use a replace operation to fill the correct field with the ID values. Then change the definition of the original relationship to be based on ID.

[LaRetta]

Hi Jesse,

Listen to Bruce on changing from name to ID for your relationships. But that's not why your memo print fails. Your Ship_Client table appears to be your client table, where you should only have one record per client? At least your memo you are printing is based upon Ship_Client but the BODY is based upon Main. In other words, when you are in Main, you are standing in your MANY side (many quotes) and not your ONE side (one client).

Select Layouts > Layout Setup and change 'Show records From' to point to your Main table instead of Ship_Client. Then change your script - REMOVE the GTRR. A simply layout switch will keep you on the same record you were viewing (in Main) and switch you to memo format (also based upon Main). But, as Bruce says, it's a bit difficult to understand what you're doing here. So try that and see if it gives you the results you expect. :wink2:

[Jesse Stolow]

I wasn't able to figure out how to get bruce's directions to work. How do you set up a replace operation? I am sorry I am so slow with this.

I was able to follow LaRetta, and now if I manually go to the Memo layout, everything shows up as it should--keep in mind I still have the relationship based on the names only. But when I run the new script, it still shows up all my records from Main.

I just don't know how to set up the relationship using IDs and how to get things to replace numbers with other numbers in other tables. I guess it is hard for me to wrap my head around.

[bruceR]

Example for Manufacturer/Order

1. Backup your data!

2. Go to Manufacturer table definition and create a field MFG_ID or whatever want to call it and define it as an auto enter serial number.

3. Place a copy of MFG_ID on your Manufacturer layout.

4. In browse mode, click in this field and notice that the Records:Replace FIeld Contents... menu option is now availalbe.

5. Choose this and perform the replace using the serial number option.

6. Go to Orders table definition. Change the name of field "Name" to "fkMFG_ID"

7. Go the Order form layout.

8. Find all records

9. Click once in the Namefield (now fkMFG_ID) Click in the field, not on the drop-down arrow/

10. Notice that Records menu "Replace Field Contents..." option is now enabled.

11. Go ahead and choose replace, use calculated result , choose Manufacturer::MFG_ID

12. Now go into define database and change the relation connecting Manuafacturer with Manufacturer Orders so that it is based on field MFG_ID and fkMFG_ID.

13. You are going to have to change the value list "Manufacturer" so that it is based on MFG_ID, also display second field "Name"

[Jesse Stolow]

Here is a copy of the file with some data entered into it. You can see that if you create a third record in the Main layout and pull down a client from the list, you will be unable to generate a printable Memo since we have already used that client before.

Additionally, while I followed Bruce's instructions, the Script for this printable Order Form still does not show the correct record, or does it even show the manufacturer's info on the layout anymore.

Total_Window_with_data.fp7.zip

[bruceR]

The script DOES print the correct order record.

You should look at the order examples such as Business Tracker or FileMaker Business Productivity Kit or some of the example files that come with FileMaker. Line items will need to be identified in individual line item records. You will need that for detailed order tracking, summaries by part number, back orders, etc. You will also need to set up Designer and Client primary keys as you did with manufacturer - linking by name isn't a good idea. What if you correct somebody's incorrectly spelled name? You no longer have any connection to their order history.

Edited August 29, 2006 by Guest