Is FM9 ESS a two-way street?

[plh212]

I am new to FM9 but not FM. I just purchased the updated version.

I am trying to understand how to set up the relationship between FM9 and MySQL after having setup a ESS. It looks like I am able to read information from MySQL and changing them in FM but not exporting them from FM9 into MySQL when creating a new record.

Until getting FM9 I was exporting files (FM 8.5) saved in .csv (with a script) and then importing them in MySQL using Navicat. It worked fine. But any alteration of any record in FM meant saving six different files and exporting them in six different tables in MySQL. I thought I could achieved the same result with less hassle by connecting directly FM9 and MySQL so they can "talk" to each other. But I haven't figure out yet how to - should I say - "sync" the db in FM9 with MySQL data. Can this be achieved?

[Genx]

When you make a change in an ESS linked table in FileMaker it is sent directly to MySQL. Don't think of it as a FileMaker table or occurance - it is a link only to MySQL - all changes are made there.

[plh212]

Yes. You are absolutely right it does and I have been able to achieve this so far. What I haven't been able to achieve though is creating a new record in FM9 and adding it to the MySQL database.

I have now all my fields in one FM layout "reading" from five MySQL tables. When changing one field into an existing record it gets changed and recorded in MySQL. But as stated before when creating a new record I cannot get it to be added to the MySQL db.

Further I have fields that were previously set up to be calculated from existing fields as soon as information is entered (in FM) and these calculated fields are now ignored and left blank.

What do I need to do or add in order to achieve such a feast?

Philippe

[Genx]

Taking it one at a time - new records should just get added to the SQL system if you add a record on a layout based on a SQL sourced table occurance (i.e. based on an ESS table occurance).

[plh212]

I am not sure I clearly understand your statement and would appreciate some clarifications. I gather that new records CANNOT be added from the same FM layout as the one used for "editing" or change information read from MySQL db?

If so what is the actual calculation of function to "write" a new record to MySQL db?

[Genx]

I am not sure I clearly understand your statement and would appreciate some clarifications. I gather that new records CANNOT be added from the same FM layout as the one used for "editing" or change information read from MySQL db?

Wrong. You can add, edit, search and delete via FileMaker through ESS.... Are you actually using ESS or are you using the "ODBC" script step?

[plh212]

I am getting confused !

I refered to public_techbrief_ess_en.pdf which was sent to me by Kyle from FM.

"...Terminology

Here is a brief recap of important terms for understanding the ESS feature set.

ESS: External SQL Sources, a new feature in FileMaker Pro 9.0 that allows data from select SQL data sources to be referenced dynamically from a FileMaker Pro database.

ODBC: Open Database Connectivity, a widely-used interface for communicating with databases. ESS works via ODBC connections.

ODBC driver: a piece of software that supports ODBC communication to a particular ODBC data source.

..."

So following on these descriptions I believe I do have an ESS implemented with an ODBC connection.

I am using the driver from Actual Technologies v. 2.7 for Mac OSX.

In "Manage -> External Data Source" it shows I created a connection type ODBC.

[Genx]

Yes that's all correct - there are however other references to ODBC in FileMaker so I wanted to make sure we were talking about the same thing.

Now, have you selected a Table from your ODBC source and placed it in the relationships graph? If so, have you then based a layout on that Table Occurance?

[plh212]

Yes I do believe so. I did set up relationships between my FM layout and five tables of MySQL db. My fields in FM then get data from MySQL using the "Display Data From" in the field setup. And that's why so far I have been able to read and alter data of existing records with no problem. But I must be missing something when it comes to creating a new record.

First as stated in earlier posts I cannot get the automatic filling of fields I had set up based on information set in other fields since I am now asking the FM fields to display data from the MySQL db. And then cannot add new records with information entered in the FM layout. There is something I haven't understand yet.

[Josh Ormond]

I did set up relationships between my FM layout and five tables of MySQL db. My fields in FM then get data from MySQL using the "Display Data From" in the field setup. And that's why so far I have been able to read and alter data of existing records with no problem. But I must be missing something when it comes to creating a new record.

Once you add the MySQL table (datasource) to the relationship graph, you should see another layout with the same name. That layout is using the MySQL table to display data (see pic below - I added a MySQL table named Consumer ODBC to the relationship graph). No special relationship or FM table needed to access the data.

You can't add records using the layout in your explaination because that layout uses the FM table as its source. Yes, you can display data from the related MySQL tables in fields, but you aren't actually looking at the MySQL table.

You should have 2 layouts that are showing records from the MySQL tables. If not, you can create them. The key is that the "Layout Setup" is showing data from the MySQL table.

[plh212]

Thank you. That was my guess since the beginning... that's why I used the title to this thread "Is FM9 ESS a two-way street?". Which you confirm it is not.

I don't have a problem setting up a second layout to add up records but it looks like, if I am not mistaken, that you can only display data from one MySQL table in each layout... which would be very inconvenient, to say the least.

[Genx]

Thank you. That was my guess since the beginning... that's why I used the title to this thread "Is FM9 ESS a two-way street?". Which you confirm it is not.

He didn't confirm any such thing.

I don't have a problem setting up a second layout to add up records but it looks like, if I am not mistaken, that you can only display data from one MySQL table in each layout... which would be very inconvenient, to say the least.

You can use portals etc. as you can normally with FileMaker layouts - the same rules apply to anything based on an externally sourced table.

[plh212]

I would like to try to explain what I meant by "Two-way Street". I set up FM9 in hope to exchange data back and forth between a layout and a MySQL. If you read the beginning of the thread you will read how I set up this layout (with portals to the five different MySQL tables I am using).

As suggested by jmormond I needed to set up another layout to be able to add a record (which is fine by me, but we are no longer talking about a two-way street but two one-way street). I tried it and it works fine but you can ONLY connect to ONE table at a time if I am not mistaken... so now we're talking about a restricted-alternative-one-lane street... a totally different animal than what FM is claiming in their advertising for FM9 !!

I respect and appreciate very much your help in trying to help me resolve my issue but... can I ask a stupid question: "Your profile states FM 8.5 and we're talking about FM9. Have you used FM9?"

[Josh Ormond]

I would like to try to explain what I meant by "Two-way Street". I set up FM9 in hope to exchange data back and forth between a layout and a MySQL. If you read the beginning of the thread you will read how I set up this layout (with portals to the five different MySQL tables I am using)

I suspect you don't understand what a layout is. It is simply a view into a table. You can do what you are trying, just not the way you are doing it.

As suggested by jmormond I needed to set up another layout to be able to add a record (which is fine by me, but we are no longer talking about a two-way street but two one-way street). I tried it and it works fine but you can ONLY connect to ONE table at a time if I am not mistaken... so now we're talking about a restricted-alternative-on e-lane street... a totally different animal than what FM is claiming in their advertising for FM9 !!

It does exactly as FM advertises. Granted, there are a few limitations that need to be accounted for...but it still allows you to both read from and manipulate data in a MySQL table.

The limits you are talking about are similar in any database front-end. It may be good to review some relational database theory...and proper structure technics.

I respect and appreciate very much your help in trying to help me resolve my issue but... can I ask a stupid question: "Your profile states FM 8.5 and we're talking about FM9. Have you used FM9?"

Trust me...regardless of what his Profile says, Genx knows his stuff. He would provide his valuable input if he didn't know what he was talking about.

It might be best to get back to the task at hand...any questions we can help get your file where you need it???

Edited October 8, 2008 by Guest

[plh212]

Trust me I truly appreciate all the help I can get from you or genx... and as stated I was just asking a "stupid" question...

So let's get back to the issue(s) I am trying to resolve. As stated in the beginning of this thread I would like to avoid having to export data entered in an FM layout about our product line. All the data are then split and exported via .csv files into five different tables in a MySQL db. It is a tedious and time consuming process.

I would like to keep (if possible) our original layout. I have established an ESS connection. I have then changed the view of each field to read the data of one of the corresponding data in of the five MySQL tables.

I was then able to read AND alter data but NOT to create or add a new record. You suggested I use the "Layout setup" to read data from the table. But as mentioned before you can only read from one table at a time with this setup. Not to mention that in both cases I lost the ability to have data of certain fields automatically created by calculating info from other fields.

Going back to portals (which might well be the way to go) I still cannot get a new record added to the MySQL db.

So maybe you can explain to me since I am new to FM9 what is the difference between using the portals (drawing data from the five different table) and the layout setup (which obviously limit the "reading" of data to one single table).

The limits you are talking about are similar in any database front-end. It may be good to review some relational database theory...and proper structure technics.

Yes I understand this extremely well but by the same token folks at FM assured me that what I am trying to achieve is exactly why FM9 has been designed and created for.

[Josh Ormond]

I was then able to read AND alter data but NOT to create or add a new record. You suggested I use the "Layout setup" to read data from the table. But as mentioned before you can only read from one table at a time with this setup. Not to mention that in both cases I lost the ability to have data of certain fields automatically created by calculating info from other fields.

Going back to portals (which might well be the way to go) I still cannot get a new record added to the MySQL db.

Couple ways to handle, here is one way.

A script. Creating a new record only creates a new record in one table. Its the nature of the beast. One script could cycle through the different layouts (one for each of the MySQL tables) creates a new record in each as it goes and land back on your original layout. Throw in a freeze window step at the beginning and the user doesn't even see it happening.

This will require a proper relationship. For example, in the FM table, Product::ProductID = MySQL1::ProductID (same relationship for each MySQL table).

The script basically looks like this:

Set Variable [$ProductID, Value:ProductID]

Loop

Go to Layout ["MySQL1" (MySQL1)]

New Record/Request

Set Field [MySQL1::ProductID; $ProductID]

Go to Layout ["MySQL2" (MySQL2)]

New Record/Request

Set Field [MySQL2::ProductID; $ProductID]

//same steps through your last table

End Loop

Go to Layout [original layout]

That will create a new record in all the tables and related them based on the ProductID. Which you can then use a portal, individual fields, or the like.

Trust me I truly appreciate all the help I can get from you or genx... and as stated I was just asking a "stupid" question...

No problem, just wanted to be clear and get back on topic. I always try my best to remember that you can't EVER, EVER read emotion into text (I do better on some days, worse on others).

So maybe you can explain to me since I am new to FM9 what is the difference between using the portals (drawing data from the five different table) and the layout setup (which obviously limit the "reading" of data to one single table).

The layout is a direct look into a table...say for instance, the Customer table. Everything you do on that layout is done in the context of the Customer records. If you wanted to count the number of Invoices in the invoice table, the calculation is done looking from the customer table. Add a new record, it gets added to the customer table. To create an invoice, you need to do it through a layout that is based on the invoice table (there is another way to create a record in a related table, see next paragraph).

Portals are a window into a related table (each portal = 1 table). Like above, you could put a portal onto the customer layout that displays data from the invoice table that are related to that customer record. You can set up a portal so you can create records in the related table, but it is done inside of the portal.

Yes I understand this extremely well but by the same token folks at FM assured me that what I am trying to achieve is exactly why FM9 has been designed and created for.

And it will. The setup you are using is what makes it a little confusing. Not that it won't work, it's just different than how most might approach it.

For that, we need to know more about what you are trying to do, and why. Details about the purpose of the db, structure and such.

It seems, at least to me, odd that you enter data into 1 table, then split it into 5 tables. Not that you don't have a legitimate reason for doing so...just odd. Might be a better way or might not, but we would need more info on your situation in order to offer viable suggestions. :grad:

[plh212]

Thank you Josh very much for your help. Since I am still not completely clear on what is the best approach to get what I am trying to achieve done I am going to try to explain why I am doing what I am doing.

And it will. The setup you are using is what makes it a little confusing. Not that it won't work, it's just different than how most might approach it.

For that, we need to know more about what you are trying to do, and why. Details about the purpose of the db, structure and such.

It seems, at least to me, odd that you enter data into 1 table, then split it into 5 tables. Not that you don't have a legitimate reason for doing so...just odd. Might be a better way or might not, but we would need more info on your situation in order to offer viable suggestions.

First and foremost I did not split the "product data" into tables I inherited this structure when I purchased x-cart one (pretty well known) e-commerce cart. I cannot say that the programming is clean and straightforward... but guess what... it works. The product data is split between six (not five as erroneously stated since the beginning of this thread) tables (for product (51 fields), product_price (5), product_link (3), product_options (6), product_thumbnail (4), and product_image 11)). Instead of using what I considered their cumbersome interface to add a product I created a one page layout in FM with all the parameters needed for x-cart to start displaying correctly the newly added product with its associated image.

Some of the fields needed for some tables (like the product_thumbnail_path and product_image_path a long and awkward path that gets automatically "calculated" in FM based on the productID (I have only to enter the ProductID once at the top of the page in my layout). And since the software has been designed with international sales in mind they are some fields in some tables that do not show on my layout this allow for a better visual control for each product (I used tabs as well) of what needs to go and what does not need to go into each table.

I have written scripts that select which fields are needed to write for each newly added record in their corresponding tables. Each file gets created by the six scripts (one for each table) and then imported in Navicat in each table. The problem is that you have to halt the site first, and re-importing each and every single record of the entire db every time you add even just one product or changing a pricing on a series of products. Not terribly efficient !!

You wrote at the beginning of your post a script. If I am not mistaken this script has to be "internally" added to the layout (in a field?) and when a new record is added it is triggered to write to the proper MySQL tables?

[Cortical]

2 things:

1. set up a simple new test FileMaker database, add the ESS. on an FM layout in table view, create a new record; i.e. create a new MySQL record from the FM interface.

Does the record count in the FM file change? (rolodex in the status area)

Assuming a new record has been created, if you then look at the MySQL table itself, you probably won't see the new record until you do a refresh (reload in Navicat).

Examine the refresh/flush sql script step; it won't necessarily help in this instance but you need to establish that...

2. if you are trying to create new MySQL records via portal entry in the FM interface; is acrr a familiar term yet? Establish in your test setup that it can be done via an acrr relationship.

[Vaughan]

acrr? Wossat?

[Genx]

Allow Creation of Related Records?

[Vaughan]

Oh yeah, thanks Genx. mmwbfamt. wastkta? hdpkuwatia? iagtigpbth! itihalldn.

(my mind went blank for a moment there)

(was I supposed to know that already)

(how do people keep up with all these internet acronyms)

(it's a good thing I get paid by the hour)

(I think I'll have a little lie down now)

[Genx]

I was just guessing lol... The question mark indicated that - not "OH EM GEE (OMG), holmes HOW COULD YOU NOT KNOW THAT". The same thing happened to me the other day with "SRRO".

[plh212]

Examine the refresh/flush sql script step; it won't necessarily help in this instance but you need to establish that...

2. if you are trying to create new MySQL records via portal entry in the FM interface; is acrr a familiar term yet? Establish in your test setup that it can be done via an acrr relationship.

Thanks for the information. I am a little bit further up the ramp (I have an "acrr" setup) since I have been able to record new records in MySQL... without clearly understanding exactly how things works and not being able yet to record to all six tables.

Josh mentioned that a layout in FM is basically a window into a db. Ok, I get the concept, but it is still confusing to me as to what the difference is between having a layout using portals (of which you can have plenty of looking at different tables in MySQL) versus having a layout setup displaying one and ONLY one MySQL table?

I see that using a "Layout Setup" with one MySQL table I could alter the fields and re-enter the much needed calculations I lost when setting up the portals in the other layout (they are still there showing up in fields but don't actually work).

Is there a trick or method to have a Layout Setup showing more than ONE table since my original layout was set to export to six different MySQL tables?

[Josh Ormond]

Thanks for the information. I am a little bit further up the ramp (I have an "acrr" setup) since I have been able to record new records in MySQL... without clearly understanding exactly how things works and not being able yet to record to all six tables.

Did you follow the steps I gave you? You need to create a new record in each table and relate it (by entering the id in the match field). Then you should be able to enter data into the portals. But you have to create the record in each table first.

Josh mentioned that a layout in FM is basically a window into a db. Ok, I get the concept, but it is still confusing to me as to what the difference is between having a layout using portals (of which you can have plenty of looking at different tables in MySQL) versus having a layout setup displaying one and ONLY one MySQL table?

Just to clarify, I said a layout is a window into a table(and only one table), not a db, since a db is typically a collection of tables.

Portals merely display related records from another table (that is related).

Picture each layout as an island. Each representing a different family of people (and the people = records). A portal would be like you on Island1 looking at a list of people you are related to on Island2. Having a baby on Island1 doesn't automatically make a baby on Island2.

When you "Create a New Record" only the table you are currently on gets a new record. To create records on each related table...you have to go to each table and create a new record.

I see that using a "Layout Setup" with one MySQL table I could alter the fields and re-enter the much needed calculations I lost when setting up the portals in the other layout (they are still there showing up in fields but don't actually work).

This will work depending on your calculations. I haven't had any trouble (once I get the calc right) using calculations in portals.

Is there a trick or method to have a Layout Setup showing more than ONE table since my original layout was set to export to six different MySQL tables?

One Layout, One Table. You can do what you are looking for you just need to follow the advice given here. You keep coming back to the same response about not being able to have 6 tables on one layout.

[plh212]

...what the difference is between having a layout using portals (of which you can have plenty of looking at different tables in MySQL) versus having a layout setup displaying one and ONLY one MySQL table?

It looks like this question was not addressed even though it could mean a lot to proper design/layout.

Picture each layout as an island. Each representing a different family of people (and the people = records). A portal would be like you on Island1 looking at a list of people you are related to on Island2. Having a baby on Island1 doesn't automatically make a baby on Island2.

Could we go back to records, tables and db... I am not really good with family tree

Just to clarify, I said a layout is a window into a table(and only one table), not a db, since a db is typically a collection of tables.

I have a layout using portals and I can look up at more than ONE table of the same db. Or am I fooling myself?

Portals merely display related records from another table (that is related).

I am sure you did not intend to but this sentence is confusing and end up contradicting what you previously stated (one quote above this one).

[Josh Ormond]

Sorry, was out for a couple weeks. My wife just had a baby.

Could we go back to records, tables and db... I am not really good with family tree

It was a stab in the dark. Baby on the brain, I guess.

I have a layout using portals and I can look up at more than ONE table of the same db. Or am I fooling myself?

You are getting distracted by technical terms, I believe.

There is a major difference between working/looking at a table in a layout, and seeing related record from a portal.

In order to create new records in each of the tables represented by portals, you need follow my instructions above (a script that goes to each table's layout and generates a new record and inserts the match field's value). This is basic relational db activity. I don't know how else to explain it so you understand.

I am sure you did not intend to but this sentence is confusing and end up contradicting what you previously stated (one quote above this one).

Again, viewing records from another table in a portal is not the same as looking directly at the table and manipulating data/records.

"...what the difference is between having a layout using portals (of which you can have plenty of looking at different tables in MySQL) versus having a layout setup displaying one and ONLY one MySQL table?"

It looks like this question was not addressed even though it could mean a lot to proper design/layout.

This one is hard to comment on...it all depends on what you want to accomplish and how you want to do it.

Where are you starting from? What layout are you using to display the portals from the MySQL database. How does the db know what records to show you in the portals?

[plh212]

Sorry, was out for a couple weeks. My wife just had a baby.

Congratulations!! If this is your first one... welcome to the "Events that changed your life forever" Club !

In your absence I made some progress and was able to create a layout from our MySQL customer's base which allow me to access in real time all the needed information without having to import them constantly. This layout includes a portal displaying all the "Ship To" addresses our customers enter. Ex: One customer may want to send a gift to relatives. We now have access through this portal to ALL their "Ship To" information. I successfully managed to alter and edit information through this layout and see them recorded to the MySQL db.

The next task to tackle is the "Createa New Record" not for the customer's base since they do it online but for our products list. And this is where I still need guidance.

In order to create new records in each of the tables represented by portals, you need follow my instructions above (a script that goes to each table's layout and generates a new record and inserts the match field's value). This is basic relational db activity. I don't know how else to explain it so you understand.

I read your script. Question: Where should this script resides? In a button "New Record" so that layout is blank and then information is filled or should a "Record Record" button be set and depressed at the end when the layout is filled out with all information thus recorded them in all the tables (Total of six)?

Edited November 7, 2008 by Guest

[Josh Ormond]

Congratulations!! If this is your first one... welcome to the "Events that changed your life forever" Club !

Yup, first one. And you aren't kidding. I usually adapt to things pretty quickly, but I still feel clueless. Everyone assures me this is normal for the first 18 or so years.

In your absence I made some progress and was able to create a layout from our MySQL customer's base which allow me to access in real time all the needed information without having to import them constantly. This layout includes a portal displaying all the "Ship To" addresses our customers enter. Ex: One customer may want to send a gift to relatives. We now have access through this portal to ALL their "Ship To" information. I successfully managed to alter and edit information through this layout and see them recorded to the MySQL db.

Good to hear.

I read your script. Question: Where should this script resides? In a button "New Record" so that layout is blank and then information is filled or should a "Record Record" button be set and depressed at the end when the layout is filled out with all information thus recorded them in all the tables (Total of six)?

A "New Record" button sounds good to trigger the script. The trouble I am having is that I still don't completely understand what you are trying to do. Can't seem to picture it in my head. Can you describe the structure (tables, fields, relationships) and a layman's step-by-step as to what you want it to do when you "Create a New Record".

Maybe that will help me see the big picture.

[Vaughan]

The first 18 years are the hardest.

[plh212]

Everyone assures me this is normal for the first 18 or so years.

Make that 22... don't forget college

The trouble I am having is that I still don't completely understand what you are trying to do. Can't seem to picture it in my head. Can you describe the structure (tables, fields, relationships) and a layman's step-by-step as to what you want it to do when you "Create a New Record".

Simple: But I would like to point out first as I mentioned in earlier posts that I inherited this structure (MySQL structure) and cannot change it.

Each product has six tables (all with a relationship on "ProductID")

which are: product, pricing, options, link, thumbnail, image. Some table (options and link) have a one to multiple relation (since one product may have more than option or link).

Currently I can, through portals, look at all the options or links available for one product and am able to modify them all and see them recorded in MySQL db.

The idea is to have one and only ONE layout showing all six tables as I have successfully been able to achieve for viewing and enter all pertinent information regarding that product then record data to all six tables. Obviously some information (ie. "productID) have to be recorded six times (one for each six tables). Also some calculations or data entered should stay from the previous entered product after the recorded is done and show in fields for the new record.

Say you want to enter 21 identical products the only difference between them being flavor (we sell food ingredient), and pricing but all other field such as description, links etc... will carry over from the previous entered product. I had setup my previous layout to do exactly that included entering automatically long paths (for thumbnail and images) and adding the "ProductID" at the end of the path.

Based on my failure to be able to have it working for displaying date as dd/mm/yyyy and not as timestamp in Unix format I am at lost as how to achieve this.

What I cannot display right now (for example in customer's db) is fields that require a calculation. The date a customer register is recorded in a timestamp field in Unix format (nbr of seconds since Jan 1, 1970). If the field "date" in my layout is setup to display as "edit box" the data "::date" from table "MySQLdb_customertable" it will show the timestamp 1.209e+09 and when clicking on the field change to 1208984557.

I tried adding a calculation in both enter and validate in the Manage database fields (for the MySQL table in question) but it does not work and still display the same data. I have the feeling the calculation should be done first and then display the result but I am not sure how to achieve that.

[plh212]

What I cannot display right now (for example in customer's db) is fields that require a calculation. The date a customer registers is recorded in a timestamp field in Unix format (nbr of seconds since Jan 1, 1970).

Strangely FM GetAsTimestamp function translates Timestamp recorded in seconds from date starting at 1/1/0001. So using the calculation GetAsTimestamp ("1/1/1970") + GetAsTimestamp (dateStoredInMySQLdb) I was able to display the date though... incorrectly. Date is off by 2 days !! What I am doing wrong?

>> Added: I now changed date in the calculation to 1/3/1970 to display the date correctly ! <<

I believe it must be possible to use the MySQL function which return directly the date by querying... but I do not have not any experience with that. So I should try that next.

I have the feeling the calculation should be done first and then display the result but I am not sure how to achieve that.

I am now able to display this information using a new field to do the "calculation" using the data from the MySQL db and then displaying the result using the newly created field.

[plh212]

This will require a proper relationship. For example, in the FM table, Product::ProductID = MySQL1::ProductID (same relationship for each MySQL table).

The script basically looks like this:

Set Variable [$ProductID, Value:ProductID]

Loop

Go to Layout ["MySQL1" (MySQL1)]

New Record/Request

Set Field [MySQL1::ProductID; $ProductID]

Go to Layout ["MySQL2" (MySQL2)]

New Record/Request

Set Field [MySQL2::ProductID; $ProductID]

//same steps through your last table

End Loop

Go to Layout [original layout]

That will create a new record in all the tables and related them based on the ProductID. Which you can then use a portal, individual fields, or the like.

I created a button with this type of script "New Record". Unfortunately since some fields in the table, other than productID, have a non-null requirement so the script starts acting erratically, meaning not able to add a new record to another table.

I was thinking of creating a long list of variables in a layout and then "record" data in each table by using a script. Is this feasible? I think so but maybe not a clean design. What would you suggest instead?

[Josh Ormond]

Add a commit records step after you set the field...check the option to skip data validation.

Set Variable [$ProductID, Value:ProductID]

Loop

Go to Layout ["MySQL1" (MySQL1)]

New Record/Request

Set Field [MySQL1::ProductID; $ProductID]

[color:orange]Commit Records/Requests [skip data entry validation]

Go to Layout ["MySQL2" (MySQL2)]

New Record/Request

Set Field [MySQL2::ProductID; $ProductID]

[color:orange]Commit Records/Requests [skip data entry validation]

//same steps through your last table

End Loop

Go to Layout [original layout]

That should allow the script to continue...

[plh212]

In order for me to understand the mechanism behind FM9 ODBC behavior and how I should set up my layout I have created a small db composed of three tables (product, option, price). The first table has 3 fields: ProductID (int 6 digits), prod_description (text), wholesale (Y or N).

The second table has 2 fields: productID, options (text). The last table has 2 fields: productID, price (decimal 2).

I have a relationship based on productID with "Allow creation... relationship" and "Delete related...other table" and "Sort Records (on productID)" checked on both sides.

The layout display the data of all three tables.

In order not to re-enter the productID three times I have setup the field productID in both price and option to be a calculated value in both Auto-Enter and Validation where productID = product::productID. And this does not really work with this configuration.

I have created a button "New Record" with the following script attached to it. It look like this:

Set Variable [$prodID; Value:product::productID]

Loop

Go to Layout [original layout]

New Record/Request

Set Field [product::productID; $prodID]

Commit Records/Requests [skip data entry validation]

Go to Field [price::productID]

New Record/Request

Set Field [price::productID; $prodID]

Commit Records/Requests [skip data entry validation]

End Loop

Go to Field [product::description]

Even with Commit R/R on I still get an error message for entry in required field like price. I also end up with duplicate key entry in productID stopping the script. Somehow it creates one record in the product table and none in the price and options tables. All the data are still showing in the layout without a way of getting saved to the remaining two tables.

Also very little information exist on the option "repetition" in the variable setup. What does this number ensure? From Help: "Repetition is the repetition (index) number of the variable you want to create. If no repetition is specified, the default is 1." Does this mean I should enter the nbr of times my variable is going to be used? I set it up at 1 and then 3 with not much difference in the result.

[Josh Ormond]

The Script...it is creating a duplicate record because the script tells it to. I will comment on each step and hopefully you can see why it does what it does.

Set Variable [$prodID; Value:product::productID]

Loop

Go to Layout [original layout] [color:orange]//Don't think this is what you want to do. You need to go to another layout, one of the ones attached via a portal.

New Record/Request [color:orange]//because you are still on the original layout, it creates a new record there.

Set Field [product::productID; $prodID]

Commit Records/Requests [skip data entry validation]

Go to Field [price::productID] [color:orange]//this step keeps you in the same layout and the next step creates another new record in the same table as the last step. Should be "Go to Layout".

New Record/Request

Set Field [price::productID; $prodID]

Commit Records/Requests [skip data entry validation]

End Loop

Go to Field [product::description] [color:orange]

Tell me more about the Options and Price. Do the Prices go with the different options, or are they both related directly to the product. Let me know and then I will upload a small sample file of the basic setup.

Also very little information exist on the option "repetition" in the variable setup. What does this number ensure? From Help: "Repetition is the repetition (index) number of the variable you want to create. If no repetition is specified, the default is 1." Does this mean I should enter the nbr of times my variable is going to be used? I set it up at 1 and then 3 with not much difference in the result.

Repetitions have a very limited use with all the other features FM has added. In general, unless you are well versed in how they work, keep this rule of thumb in mind: "Repetitions are EVIL". It'll save you some heartache until you get a firm grasp on database theory.