plh212

Cannot find this topic on Filemaker on the GO --> Filemaker Go for IPhone & iPad. Has it been renamed something else?

Hello Barbara, That's an excellent suggestion and I wished the cart software designer had thought of it, unfortunately I must deal with existing tables and fields already setup and can only access information with what we do have. Reconfiguring the tables would means re-configuring a lot in the program itself. Unfortunately not an option. Thank you for your help. Philippe

Created a layout to display a complete record pulled from different MySQL tables with no problem only for one table. The relation is a one-to-many and no matter what I tried scripts, searches with AND in record searching I cannot pull the correct record. Each of my products is associated with one, two, three or more categories. One of these categories is marked as "Main Category" and has a "Y" it the field "Main". I only one to display the main category in my layout (I am using it as a header to have together in the same category all the related products) for each of the record being displayed. So we have: Products Table with a unique Product_ID and Products_Cat with 3 fields: Product_ID, Category_ID, and finally Main with value of Y or N,. None of these fields being unique by itself, only the combination of the three is unique. Example: 345609, 220, N 345609, 245, N 345609, 223, Y 120067, 220, N 120067, 245, Y 120067, 238, N 120067, 223, N The pointer stays on the first record found in Products_Cat and does not display the correct category for the product. The table is indexed on Product_ID and Category_ID. I tried having among other thing a match search with Product::Product_ID = Products_Cat::Category_ID AND Products_Cat::Main = "Y" but that does not do the trick. Some help would really appreciated. Filemaker Pro 11 MySQL db

Thank you The Shadow and LaRetta for your help. I have used successfully: ">123000<123500" entered directly in the field number and it returns all numbers between 123001 and 123499 (this is good for searching invoices for example) but for situations described by shadows such as "##30##" in a product code field I haven't yet find out how to address the problem. To The Shadow: I am searching not a FM db but a MySQL db through an ODBC FM connection where the # wildcard does not work. Your recommendation of using "123000...123500" directly entered in the number field works actually better than mine since it returns all invoices between 123000 and 123500. It neatly replaces the > or = to and < or = to I was looking for :

If I understand correctly I should include a SQL LIKE query in a script since the "Find" cannot be entered directly in the field searched?

For finding records starting with "213" in a field number with 6 digits in FM one would use "213###" and it would return ALL invoices starting with 213. Now that we have an ODBC connection to our MySQL db the wildcard search returns an error: "This field is defined to contain numeric values only. You must enter a numeric value". My question is: Which wildcard character should I use to replace the "#" when reading MySQL db through an ODBC connection? I unsuccessfully tried "%" and "_".

Yes. It does work if Ipause before. I am not sure i understand the complete logic behind it since I saw it as a pause and not a stop but it does definitely work doing it your way. You win :

Maybe I changed something else that I don't see... but I haven't been able to make it work. I had set my Pause/Resume after the first "Set Variable". I don't know why this is not working. I am adding the sample files. 1228499706-Create_Records_in_Portal_tables-1_Folder.zip

Indeed. Make sense! I brought them back and follow your advice for not listing them in the layout list. Thank you. I managed to wreck your example! I need to be able to enter the productID (unique, int(6)) and have added a Pause/Resume (indefinitely) to be able to fill the field and then resume the rest of the script by clicking onto "Continue" when my ProductID satisfy our requirement. Somehow after clicking onto Continue it does not copy the variable to the "ProductID" fields in the other tables and then just close the record without being able to enter the options or prices. Should I split my script after the pause to start a new script or am I wrongly using this Pause/Resume?

Thank you! Yes it does work beautifully... meaning the way I had intended to have mine working But, hopefully it will help me understand what I was doing wrong. I noticed something quite intriguing. When you delete the two layouts that have been created automatically when adding the portals you can no longer enter data in the options and price portals ! How are these two layouts related to the product layout you created? I can understand the effects of deleting fields and/or tables, but layout !!

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.

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?

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 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.

Make that 22... don't forget college 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.

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. 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)?