Related fields

[Bikeman17]

Hi there,

I just converted pro 6 databases into pro 9. Some functions don't seem to work as before. With pro 6, I used to add items from my Inventory Entry database and send that information to the Inventory database. Fields located in the Inventory db are looked up through the Product Code field. With pro 9, those fields do not look up properly.

See the attached folder. Feel free to change things to make it work.

Solution.zip

[mr_vodka]

1. you should go through all your scripts and update them. There are layouts missing etc.

2. you should get away from copy and pasting data.

3. you should read the migration tech briefs.

http://www.filemaker.com/support/whitepapers.html

conversion doc

http://www.filemaker.com/downloads/documentation/fm8_converting_databases.pdf

Actually... You should probably start with #3 first.

[Bikeman17]

OK There was a missing layout. Keep in mind that I removed useless things to make the tour easier.

Before submitting this post, I set up a script that used the Set Field command to avoid copy & paste. I have been doing that in other dbs successfully but in that case, it did not work.

I am relying on someone who is willing to take a closer look at these dbs.

Solution.zip

[Brian C]

Is there a reason why you don't merge the tables into a single file?

I just tried using your process to create a new record and it pasted some old contents from my clipboard into the inventory db... which just happened to be some text from one of my earlier posts here... You should use script parameters to pass data from one file to another if you insist on keeping the solution in separate files and triggering scripts from one file to the next...

Are you using FMP Advanced? If so, you should start using the Data Viewer and the Debugger to step through your processes so you can see what is happening as the script is executing one step at a time.

[Brian C]

One problem with the copy/paste:

You use a go to related record step from the Entry to the B table.

You have no test records for your script step to work

You have no script steps to determine wether or not you even have a valid set of records to work with prior to moving forward with the script.

You need to add more error checks into your scripts and not just assume that data will be there or that it worked the way you thought it should...

[Bikeman17]

Hi Brian

I don't mind to use a single file. I've never been through that process. If someone could show me the way, I would appreciate greatly. I don't have a clue on how data exchanges from one table to another. I developed these dbs under pro 6 to update pricing and add new items easily.

[Brian C]

Your sample just has a lot broken with it - I'm not sure how much of it is due to conversion and how much of it is due to editing your solution for the purposes of posting it for assistance.

Some of your value lists don't work because there is no table data available for it to be based on. Others are because entire tables that are being reference are not even available.

If you are looking to merge your tables - it is not a fast process really. But it may simplify your work later on.

If you have FMP Advanced, you can copy your table schema from one file to another.

Otherwise you will have to recreate your tables manually one field at a time.

You would have to recreate your relationships which is not hard looking at what you have in the sample.

Beyond that, recreating the value lists is next up and then copying and pasting your scripts and then repointing the scripts to resources inside of the file instead of outside of the file.

The biggest thing to remember is to use the Anchor Buoy technique when you are creating relationships in the Relational Graph.

You should have a single TO (table occurrance) for every primary Layout.

No primary TOs should link to any other primar TOs under normal circumstances.

Another way to look at it is this:

With the way things are setup now if you go to the RG you see your table/file in the center usually and all the support tables surrounding it.

It is the same if all tables are contained in a single file.

you will have your primary table in the center and all the support tables that link to it.

Make sense?

[Bikeman17]

Brian

I am willing to make an attempt. I am not sure I am getting all information you posted but I will submit my db later. I hope you'll be there to help me out.

What db should I use to create the additional table? My guess is Inventory. Please confirm.

[comment]

If you have FMP Advanced, you can copy your table schema from one file to another.

Otherwise you will have to recreate your tables manually one field at a time.

Not really:

http://www.fmforums.com/forum/showpost.php?post/222917/

[Brian C]

Here is a rough attempt of merging your files into one file.

You are missing some tables so Qty On Hand, etc are not working until you add those missing tables and fix the calc fields.

Some of the scripts need to be changed but I kept things pretty much the way they are and will leave it to you to fix/update scripts.

Keep in mind it is very important to never use the first occurrence of a table in the RG (relational graph) as a support table for another table. If you must, duplicate the TO and rename it so that you can use the copy as the support table.

FYI

I added additional tables for the Value Lists and you can either use these or edit the value lists to point to the custom input values that you already had.

I like having tables for my values lists so that is why i added them.

Inventory.fp7.zip

[Brian C]

FYI:

I use the grey tables as the primary table occurrence for each table in the RG. These tables are what your Layouts will always point to.

The support tables (duplicate table occurrences of the primary table occurrences) are colored orange and have a naming scheme that allows you to know the source table and the destination table. when creating a support table, duplicate the primary, and color the duplicate orange, then give it an appropriate name which will also help you to identify it as a support table when looking at the names in a list.

This is important since you will use the support tables in many cases for the Go To Related Record script step (GTRR). and then point to a layout that is based on the primary TO.

Generally the grey primary tables are on the left and the orange support tables are on the right.

[Brian C]

One more note:

Don't forget to edit your external Data sources when done.

File / Manage / External Data Sources...

You will need to remove the references to your other external tables once you are done placing your tables into this single file.

[Bikeman17]

Gosh you're about to make my day. I guess you make the merging with a finger snap. I am using FM Advanced but never paid attention to some features because my dbs worked fine under Pro 9. Today is the day to discover new ground.

Some of your comments are way out of my league. I don't get your point about duplicating the TO. The VL layouts eludes me. I used to make the value list the way it was in pro 6 but I will used them as they are.

This is new ground for me. I went through the db and you have to be patient with my learning curve. Standing by for additional comments.

[Bikeman17]

Brian

I am willing to make an attempt. I am not sure I am getting all information you posted but I will submit my db later. I hope you'll be there to help me out.

What db should I use to create the additional table? My guess is Inventory. Please confirm.

[Bikeman17]

Brian,

I have fixed some calculations to make more sense. See the attachment.

Inventory.zip

[Brian C]

The VL layouts (I use VL as a prefix for the Tables that contain value lists) are just there to input data for the tables containing value list data. There is currently no interface to them as of yet. If you want to maintain your value lists via tables instead of the custom text value lists that you are used to doing, it takes a little more work because you have to create an interface for each of them, but it can be more flexible since you can add more fields to contain alternate descriptions if need be. Then you can use relationships to the Value list tables to display this alternate description on your records...

I only added the VL tables so that you could see a different technique for doing value lists. I left your Custom Values entered, and you can switch over to those where you think it makes sense and remove the VL tables that you don't need.

Using the normal "Custom Values" with the Edit option checked is fine too, it just depends on what your needs are.

[Brian C]

Hmmm well let me try to describe it again...

If you open your solution that you have been working on so far,

use: File / Manage / Database...

Then click on the Relationships tab at the top so you can see the Relational Graph (RG).

The first thing that needs to be said: TOs are very similar to aliases in OS X or Shortcuts under windows. They contain nothing but a link to the original and contain no actual data themselves beyond that. You can have multiple aliases with different names that all link to the same original that has it's own name which may or may not be different from the alias.

Now lets refer back to your file in the RG:

On the left you will see 3 table occurrences that are colored grey. (Inventory Entry, Inventory, Stats :

In anchor buoy terminology, these would be called anchors.

These table occurrences (TOs) are named the same as the name of the actual table that they link to. While this is not necessary, it is generally a good idea to keep it that way.

These table occurrences will be the ones that we link layouts to for display of data.

So, your "Inventory Entry" Layout uses the Table Occurrence in the Relational Graph: "Inventory Entry".

To the right of 2 of the tables you will see 2 orange table occurrences that I like to refer to as "Support Table Occurrences" which would also be known as "buoys" in anchor buoy terminology.

These table occurrences are not named the same as the original tables that they are linked to. These TOs have unique names that identify their role in the in relation to the anchor TO that they link to in the RG (relational graph).

These tables are used to link to related data from the anchor TO.

So in your File for example, Inventory Entry (anchor) links to InvEnt 2 Stats B (buoy). InvEnt 2 Stats B is used on the inventory Entry layout to display the related data from Stats B and/or to create related records.

If you went into the RG and double-clicked the line between these two tables you would be able to see that this relationship is set to allow the creation of related records.

Now that all this is defined, I can now move on to your question regarding duplication of TOs.

At the bottom of the RG you have several buttons. They are from left to right:

Add New TO (plus with TO pic)

- Creates a new TO and allows you to select the Table that the TO links to

Add New Relationship (plus with link pic)

- Allows you to select existing TOs and create links between them

Duplicate TO (double plus)

- Allows you to duplicate the selected TO or TOs. The name will change slightly since names must be unique so a number is usually appended to the end of the name when it is first created. You must then edit the name to make it something more meaningful.

Edit (Pencil Pic)

- Allows you to edit a selected TO or relationship

- Editing a TO allows you to change the original table it links to or it allows you to change the name of the TO

- Editing a link allows you to change options for a relationship between two different TOs.

Delete (trash can)

- Deletes a TO or a relational link

Ok now that you know what the first set of buttons do, I will move on to what I meant about duplicating TO's

If you want to make a new support TO or a buoy, you can either:

click the Add New TO button and then choose what table to link it to and give it a name, OR you can select an existing TO and use the Duplicate TO button to make a copy of that TO and then edit the name so that the new TO can be used as a buoy for another relationship. It is important to use colors so that you can always tell your Anchors from your Buoys.

Another way of explaining Anchor Buoy is this:

the Primary Key is contained in the Anchor, and the Foreign Key is contained in the buoy. While this is not completely true if you start linking multiple buoys together to tunnel through the relationship, it might make more sense if you are familiar with those terms.

[Bikeman17]

Brian,

Thanks for the lesson. I went under RG in the past. I wasn't familiar with the expression itself. I uploaded a new attachment. I fixed a few things. I removed the copy & paste from the scripts. I replaced them by a set of Set Field commands without any success so far. There is something's missing and I can't pin point the problem. I don't know how deep your knowledge is in that field but I am looking for a script that will transfer a line of data under Inventory Entry towards Inventory. Take a look and see what you can do. I will welcome any progress.

Inventory.zip

[Brian C]

I just downloaded your sample. Please be more specific and detailed with any future questions as it will save all of us a lot more time in helping you to learn.

On the Inventory Entry screen, your button Add Item links to the script: New Item - Inventory.

When I open this script it generates a new record and sets field contents... Problem is, you are not on the Inventory layout when these steps are occurring. I am assuming that you do NOT want to create a new record in the Inventory Entry Table at this time?

Let me try to describe what I "think" your solution is supposed to do since you have not supplied much detail.

Starting From the Inventory Entry Layout (TO: Inventory Entry)

The Inventory Entry TO contains a single primary key field:

Entry Code

Date 1 (a calc field) and Supplier (text) have unknown purposes so far as I can tell.

Assume:

User inputs data on the Inventory Entry Layout using the portal.

The portal uses a support buoy TO "InvEnt 2 Stats B" where the relationship is setup to allow creation of related records in Stats B.

I don't have a working value list for the first field since this value list is based on the contents of the Inventory Table. So I input my own number in this field. The remaining fields seem to work fine. I cannot use Add New Item since the script is incomplete and will cause problems by creating a new record in Inventory Entry and then it will more than likely replace the contents of an existing record if one were to exist in Inventory because no proper navigation has yet occurred...

Your original script that the button used to be linked to seems to make sense more or less. So re-link your button back to that script and make the following changes.

Script: "New Item - Entry"

>> Add step: Commit Record/Request[no dialog]

Show Custom Dialog[Create Record? Yes, No]

if get(last message choice) = 2

halt script

else

>>Fix step: go to related record Inv Ent 2 Stats B using Layout Stats B

perform script "New Item - Stats B"

end if

>> Remove step: Go To Layout[inventory Entry]

Ok so lets move on to Script: "New Item - Stats B".

This script is very broken.

At this point all you have done is gone to the related record that the user just created from Inventory Entry. In this script, you have not done anything to "copy" the data prior to moving to the Inventory layout. You have to imagine that Set Field is the same thing as Paste but with more power and flexibility.

So to fix this script lets use global variables...

>> Add step: Set Variable[ $$productcode ; Stats B::Product Code ]

>> Remove Step: Go To Layout Inventory

>> Remove Step: Go to Record/Request/Page[last]

>> Remove Step: set field [inventory::g_product code ; stats b::product code]

perform script ["New Item - Inventory"]

Now lets look at script: New Item - Inventory

I see that you changed the script to simplify it a little. Thats fine for now. You will need to repoint your set field scripts so that they use the support table TO instead of the anchor TO for things to work.

>> Remove Step: Set Error Capture [on]

- (nothing is happening that requires this step since you removed anything that would normally cause a visible error.

>> Add Step: Go To Layout Inventory

>> Add Step: Go to Record/Request/Page[last]

New Record/Request

>> Fix Step: Set Field [inventory::Product Code ; $$productcode]

>> Add Step: Commit Record/Request [no dialog]

- this will allow you to be able to create the link necessary before continuing with the remaining steps.

>> Fix Step: Set Field [inventory::Supplier Code ; Inv 2 Stats B::Supplier Code

>> Fix Step: Set Field [inventory::Department Code ; Inv 2 Stats B::Supplier Code]

>> Fix Step: Set Field [inventory::Description ; Inv 2 Stats B::Description]

>> Fix Step: Set Field [inventory::Color ; Inv 2 Stats B::Color]

>> Fix Step: Set Field [inventory::Size ; Inv 2 Stats B::Size]

>> Fix Step: Set Field [inventory::Material ; Inv 2 Stats B::Material]

>> Fix Step: Set Field [inventory::Cost Price ; Inv 2 Stats B::Cost Price]

>> Fix Step: Set Field [inventory::Sale Price ; Inv 2 Stats B::Sale Price]

>> Add Step: Commit Record/Request [no dialog]

>> Add Step: Go To Layout[inventory Entry]

Now there are much better ways of doing this but this is the most direct way to make changes to your existing solution and have it work. Try it out and see how it works for you. Also turn on the Script Debugger in the Tools menu and then click the button and see what is happening one step at a time. Also show the data viewer so you can see what is happening with each of the fields that are being affected.

[Bikeman17]

Brian,

Thanks for the new programming. I implemented your tips but the result is an empty record under Inventory.

See my work attached

Inventory.zip