I am trying to write a script which would do a query joining two table together:

1. 
   
   
2. Find all records in CustomerOrder table where field CustomerOrder:Status is equal to "C"
   
3. Find all records in Picture table where Order:Status is not equal to "S" !!!
   
4. Find all selected records in CustomerOrder table and all selected records or Picure table where field CustomerOrder:OrderNo is equal to field Picture:PictNo
   
5. Set field CustomerOrder:Status to "R"
   
6. Set field Picture:Status to "S"
   
7. Show all records of CustomerOrder table where CustomerOrder:Status has changed
   

I am quite familiar with MS-Access and Oracle SQL and would be able to do this query quite easily.

However I am struggling with FMP, I am trying different FMP script combination and I just cannot seem to put the right syntax together.

Could someone help me going in the right direction?

Best regards!

Daniel

Could you please rename your tables, at least for the example? I have lost track of which one is the parent table and which is the child. I think you have a table of Orders (or Invoices, since they are identified by InvoiceNo) and a table of LineItems? Naming both of them Order is confusing.

In any case, there is no such thing as "selected records" in Filemaker. There is the found set (or rather found sets, depending on the window and the table occurrence), and the current record (member of a found set).

Any time you perform a find, you affect the found set in the current table occurrence only - although you can search related fields during the find, for example: search the child table for records whose parent's status is x.

Reading between the lines, it sounds like there may be some redundancy in the data design. Reconciling conflicting order statuses across two tables ("Order" and "CustomerOrder") might not be necessary if the structure were approached a little differently. At any rate, as described it is unclear.

Notwithstanding that, assuming there is a relationship that is based on a match between the Order::InvoiceNo and CustomerOrder::OrderNo fields, it should be possible to achieve something analogous to the status update procedure outlined above using script code along the lines of:

• Go to Layout[“Order” (Order)]
• Perform Find[Restore: Find: CustomerOrder::Status: “C” Omit: Order::Status: “R”]
• If[not Get(LastError)]
• Replace Field Contents[No dialog; Order::Status; Calculation: "S"]
• Replace Field Contents[No dialog; CustomerOrder::Status; Calculation: If(CustomerOrder::Status = "C"; "R"; CustomerOrder::Status)]
• Go to Related Record[show only related records; Match found set; From table: “CustomerOrder”; Using layout: “CustomerOrder” (CustomerOrder)]
• Constrain Found Set[Restore; Find: CustomerOrder::Status: “R”]
• End If

Since the method described in this code snippet is using the Replace Field Contents command, it would be unsuitable for use in a hosted solution (ie it would fail silently if any record locks were encountered). If the solution is hosted, a loop construct to update the statuses would be a better idea, since it would enable you to trap for errors in a record-by-record basis.

Many thanks for helping me!

I've made changes to my topic and I hope this is more clear.

I've got one table in a FMP dbms called CustomerOrder where the OrderNo correspond to a picture. I get another file which I import to the FMP dbms which is called Picture.

CustomerOrder =

InvoiceNo, OrderNo, Status

123456 0101 C

123456 0107 C

123456 0701 C

123456 2327 C

123455 0107 C

123454 0705 C

123453 0323 C

Picture =

PictNo,Status

0101

0102

0103

:

0107

:

0701

:

In matching the two tables together I would have the following result

InvoiceNo, OrderNo, Status

123456 0101 R

123456 0107 R

123456 0701 C

123456 2327 C

123455 0107 C

123454 0701 R

123453 0323 C

PictNo,Status

0101 S

0102

0103

:

0107 S

:

0701 S

:

More than one customer can order the same picture

In Picture table the PictNo is unique, no duplicate are allowed

If I understand correctly, you are describing a standard invoicing solution - with Invoices, LineItems and Products - see:

http://fmforums.com/forum/showpost.php?post/309136/

The pictures are the Products, and an invoice can include any number of them (through having many line items). I don't see anything about customers here - though you could have another table for them:

Customers -< Invoices -< LineItems >- Products

Now if you could explain what does the status represent in each one of your tables.

At this time Customer information is not treated in the database. This is still done manually. That will be one of the next process to put in place. At this time the most important aspect of the workflow is the registration of the order and the creating of the product to be delivered.

As per the Status here it is :

CustomerOrder:Status could be equal to C,R,G,L. These are french status code C for Ordered, R for Recorded, G for burned and L for Delivered

Picture:Status Blank or S for Selected

One of the thing I will want to do later to have records in the table display with different colors depending of the status. For example order with a status of C would be in blue because an order has been made, with a status equalt to R they would be green i.e ready to be processed for finalisation. Record status equal to L would be displayed in grey for having been delivered.

CustomerOrder:Status could be equal to C,R,G,L. These are french status code C for Ordered, R for Recorded, G for burned and L for Delivered

Wouldn't it be better to enter each step as it happens, i.e. have fields for RecordedDate, BurnedDate and DeliveredDate? The current status can be then calculated from these.

Picture:Status Blank or S for Selected

What does "selected" mean?

This mean the picture has been selected and is part of an order.

I agree with the date fields, as a matter fact those are to going to be automatically inserted in the database when each of the process occurs. The status will then be derived accordingly. However, what will be displayed to the operator will only the last modify date (RecordedDate, BurnedDate and DeliveredDate). The displaying of the Status will state which dates it correspond to.

This mean the picture has been selected and is part of an order.

That doesn't make sense, because:

More than one customer can order the same picture

If you want to know if a picture has been ordered, or - even better - how many times it was ordered, use a calculation field to count the related records in the line items table.

That doesn't make sense, because:

If you want to know if a picture has been ordered, or - even better - how many times it was ordered, use a calculation field to count the related records in the line items table.

The Status is only equal to "S" for the picture file. The reason being is the file keep increasing (records are added at the end of the Picture table) as photographs are taken. Therefore the next time I will be using the table picture table I will only select the record that are not equal to "S" (I will be fixing the start of this topic point 2 should read Find all records in Picture table where Order:Status is not equal to "S)

Is this what you're trying to do?



1 Relationship: CustomerOrder / Picture

CustomerOrder::OrderNo = Picture::PictNo

CustomerOrder::Status = Picture::C key (calc = “C”)

More I think about the Picture:Status not equal to "S" more I think I am making a mistake. Because in doing so, I will make myself not to be able order those selected picture again. An this is not what I want to do. Therefore I would remove the "Line #2 i.e Find all records in Picture table where Order:Status is not equal to "S"

As I wrote at the beginning of this topic, I am struggling with how to write the script. I will start to use what CobaltSky has suggested and will keep you inform.

Regards!

Daniel

I am afraid you have lost me...

Hello Comment

Can you send me an email address where I could send the design I am doing.

Thanks!

Daniel

Hello Comment

Can you send me an email address where I could send the design I am doing.

Thanks!

Daniel

Attach a file to a post. If you don't know how, Let me know.

Lee

A file may help, but my problem is understanding the process that the file is supposed to track.

A file may help, but my problem is understanding the process that the file is supposed to track.

I want to thank everybody who is trying to help me.

If you click in between this line

and this line you will going to my website and have access to a PDF file showing what at the CustomerOrder, Order and Picture table happend at time 0, 1, 2 and 3

I hope this will give an overview of what I am attempting to do.

Best regards!

Daniel

Daniel, I'm afraid you are shooting at the wrong rabbit. Please explain the real-life process to us, so that we can understand what this is about. I can't get my head around the fact that a photo can be ordered (by more than one customer) - but it still needs to be taken.

The only thing that comes to mind is that people are ordering a certain TYPE of picture. But then the picture status cannot be the same for all people who ordered the same type of picture.

Daniel, I'm afraid you are shooting at the wrong rabbit. Please explain the real-life process to us, so that we can understand what this is about. I can't my head around the fact that a photo can be ordered (by more than one customer) - but it still needs to be taken.

The only thing that comes to mind is that people are ordering a certain TYPE of picture. But then the picture status cannot be the same for all people who ordered the same type of picture.

Best regards!

As suggested I am trying to include this native FMP function :

Replace Field Contents[No dialog; CustomerOrder::Status; Calculation: If(CustomerOrder::Status = "C"; "R"; CustomerOrder::Status)]

I get an error message "An operator (e.g. _,-,*,...) is expected here)

In removing the word Calculation the error disappear but the replacement does not take place.

Is there another way to right this syntax and not get an error message ?

Thanks

OK, now just please walk me through this part:

How do you match the pictures on the photographer's card to the event numbers in orders?

OK, now just please walk me through this part:

How do you match the pictures on the photographer's card to the event numbers in orders?

Comment, this is exactly what makes me different than the others. Before investing developing a FMP application I've done over 12 months of research and testing to get this working.

The only thing I can say at this moment is that it does. I would not have any problems giving you this information in private.

If you want we can organize a Skype session where I will take 5 minutes to explain what I've done to get there.

Regards!

Daniel

As suggested I am trying to include this native FMP function :

Replace Field Contents[No dialog; CustomerOrder::Status; Calculation: If(CustomerOrder::Status = "C"; "R"; CustomerOrder::Status)]

I get an error message "An operator (e.g. _,-,*,...) is expected here)

In removing the word Calculation the error disappear but the replacement does not take place.

Is there another way to right this syntax and not get an error message ?

Thanks

Daniel,

The fact that you were trying to include the word "Calculation" within a calculation formula suggests that you're not at all familiar with syntax in for form I posted it, nor with FileMaker calculation syntax, for that matter. That being the case, it is going to be pretty difficult for anyone here to communicate a solution to you, without actually simply doing it for you.

I will go one step further than I did in my previous post, and provide you with a simplified file that contains the script I described. Perhaps looking at this example will give you the information you were unable to parse from the words I posted.

Orders.zip

I am not interested in the actual technique - I only want to understand where each piece of information is located. Is there a one-to-one relationship between the pictures taken and the event numbers - or are there many pictures for each event and the customer that ordered event #1234 will get all the pictures at that event?

If I had to do this, I would probably have something like the attached. This would need AppleScript to "inform" the record in Events that its pictures have been loaded. Everything else would pretty much self-organizing.

The fact that you were trying to include the word "Calculation" within a calculation formula suggests that you're not at all familiar with syntax in for form I posted it, nor with FileMaker calculation syntax, for that matter. 

Ray, you are absolutely right in your statement.

Like I've said right at the beginning I am new in using FMP and I am really not familiar with using the syntax.

However, I must say that right at the beginning I was suspicious about reading Calculation right in the middle of a function.

In providing me with an example which works helps me understand how FMP works. One of the fundamental in old programming was you had to set variables before doing a write statement.

This does not seem to be the case with FMP, am I right.?

One of the thing that I am missing is to know about the fundamentals of FMP. FMP is quite different than MS-Access and SQL Oracle.

However, I am will be investing a lot of my time in learning FMP because I find FMP is one the best DBMS which works on a MAC and that can be ported over (with modification) to a PC environment.

THANKS a millon time for sending me the zip code. I will look at it carefully.

Best regards!

Daniel

I just saw I had made a mistake in my ER model.

A Customer can make one to many Order

An Order can have one to many CustomerOrder (LineItem)

A Picture can be found in one to many CustomerOrder (LineItem)

PictureNo and OrderNo are both 4 characters text field long.

Good news!

I now have the two tables CustomerOrder and Pictures relating together.

The status in the Picture table gets a "S" value. However, the Status in the CustomerOrder table does not get changed from a "C" to a "R".

I know the Status" is equal to "C" because the first "Find" works. i.e Find only CustomerOrder record with Status equal to "C". To make sure it was the case I've replaced the "C" by a blank for a few records, and then re-executed the script.

Only the records with a status = to "C" were then selected.

I will continue to investigate why those record did not have their Status not changed to "R"

As they say "One step at a time"

You haven't answered my question - but I have a feeling you are making this about 50 times more complicated than it needs to be. Have a look at the attached demo. Go to the Products layout and change the products' status by clicking the Ready field. The go back to Orders and see the results.

JIT.zip

Is there a one-to-one relationship between the pictures taken and the event numbers - or are there many pictures for each event and the customer that ordered event #1234 will get all the pictures at that event?

Comment, the answer to this question is that there is a one to one relationships between the pictures taken and the event numbers.

As a matter fact this a AppleScript "LoadPictures" program which use to fill in the Picture table. http://fmforums.com/forum/topic/75263-looking-if-a-record-already-exist/page__p__356385__fromsearch__1#entry356385

But you could simply import them:

http://www.filemaker.com/11help/html/import_export.16.16.html#1044791

http://www.filemaker.com/11help/html/import_export.16.17.html#1028486

But you could simply import them:

http://www.filemaker.com/11help/html/import_export.16.16.html#1044791

http://www.filemaker.com/11help/html/import_export.16.17.html#1028486

The reason their not being imported is because the "LoadPictures" script goes through the directory and only takes part of the name of the Picture. For example, if pictures are called "DSC_0101.JPG, DSC_0307.JPG" only the four numbers which composed the name of the pictures are loaded in the Picture:PictNo field.

This is why I am saying the CustomerOrder:Order can be equal to Picture:PictNo.

Also I do not want to load the Pictures in the database because there are to many of them and that would take to long to load into the DBMS. I only need to know the numbers which act like an index.

You can import the pictures "as reference only" - and use a calculation field to extract the 4-digit code from the name or the path. In fact, you can even import the pictures without importing the pictures, by importing only the name and/or the path.

You can import the pictures "as reference only" - and use a calculation field to extract the 4-digit code from the name or the path. In fact, you can even import the pictures without importing the pictures, by importing only the name and/or the path.

I am really interested in this, I believe this would allow me to show the picture as a thumbnail within FMP. Am I right ?

I will then look in the forum If I could find something on how to import the pictures as reference only.

Good news, the script is now working and I am getting the exact result I was looking for. Is there a way I could have this script running in the background? I only want to process the script, get the status of the Picture and CustomerOrder tables modified. I do not need to see/visualize the result. If needed be, I would browse each of the tables.

This script is going to be executed quite often, i.e each time the photographer brings a new card and each time the Operator click the Commit Order button.

I think the good news is that your script is unnecessary. Moreover, it creates redundant data: there is no need to change anything in the line items table when a picture becomes available. Availability is an attribute of a picture, and of a picture only. The line item can "find out" if the picture is available through the relationship. It doesn't need to - and shouldn't - store this information.

In my demo, the information about product availability is stored only in the Products table - and it is being used in the Orders table directly, without any replication of data.

There is no need to change anything in the line items table when a picture becomes available. Availability is an attribute of a picture, and of a picture only. The line item can "find out" if the picture is available through the relationship. It doesn't need to - and shouldn't - store this information.

Comment, I will take more time and look at the demo you've provided me with more carefully. When you say "Availability is an attribute of a picture only." I need to digest this information.

Same thing with the statement " The line item can "find out" if the picture is available through the relationship."

If I understand right I would not need to run a script to get the same information. However, in order for a picture to become available through the relationship. I would need to load/import the reference of the picture. Am I right?

in order for a picture to become available through the relationship. I would need to load/import the reference of the picture. Am I right?

A picture could become available in a variety of ways, e.g. by manual entry (as in my example file), by importing pictures (either fully or as reference only), by importing a list of picture names, by AppleScript that does one of the above, etc.

Once that is done, all that's left is for the user to click "Burn" on those orders that are ready (i.e. all their pictures are available). Actually, this too could be automated as part of the importing script.

A picture could become available in a variety of ways, e.g. by manual entry (as in my example file), by importing pictures (either fully or as reference only), by importing a list of picture names, by AppleScript that does one of the above, etc.

Once that is done, all that's left is for the user to click "Burn" on those orders that are ready (i.e. all their pictures are available). Actually, this too could be automated as part of the importing script.

In the real life situation The ProductID and the Product fields are both the same. The only differences is that leading zero at the beginning needs to be kept. A ProductID is four characters long and always start by 01 not 1. Using an auto-enter Serial means the number for Product-ID cannot have gaps. There are gaps, for example : OrderNo, ProductID (i.e PictureNo) never start at 1. It always start at 0101, 0102, 0103, then goes to 0201, 0201. This mean there is a gap between 0103 and 0201. Being the case, this mean the picture cannot be made available through the relationship. Am I right?

In not using auto-enter serial removes I saw the automated one to many relationship disappearing. Not using auto-enter serial FMP will show the relationships to be many to many. In the present situation, the one to many relationship one to many is implicit. ProductID will never be duplicated and must allow for gaps.

What append if the picture is not available at the time or the order ? Orders still have to be made, even it the picture is not there yet. This is what the User Entry Form allows.

The system has to allow for exceptions i.e pictures that are not there at the time of the Order will be there at another time. In the real life situation the Customer always knows the first two digit of theEvent (for example 24), therefore the Operator will enter in the OrderNo the number 2499. The Operator knows on which days 24 will occur. The day of the event he will be replacing the 99 of 24 by the real number. This also means, many clients will have 99 terminating their OrderNo. On a same Invoice terminating by 99 could be found more than once. Those OrderNo with 99s are triggers, and means replacement the day of the event. At the beginning of each day the Operator is given a list of OrderNo finishing with 99. Those are to be replaced by complete OrderNo (2499 to 24xx). Those that will be happening the day after will be left with 99.

This mean there is a gap between 0103 and 0201. Being the case, this mean the picture cannot be made available through the relationship. Am I right?

No. For clarity, let's have two fields:

ProductID (Number) - auto-entered serial number

ProductCode (Text) - your 4-digit ID

The relationship between Products and LineItems CAN be based on matching:

LineItems::ProductCode = Products::ProductCode

However, in such case it is YOUR job to make sure ProductCode values are unique in the Products table. At the very least, set the field to validate as unique (this will also be reflected in the graph).

The Operator knows on which days 24 will occur. The day of the event he will be replacing the 99 of 24 by the real number.

I don't think this is a good method. If the operator knows only the event number, e.g. "24", he should enter "24" into a EventNumber field (in the LineItems table).

The next step depends on how many pictures will have a code that starts with 24. If there are more than one AND if one (or more) need to be selected out of the many, then obviously human intervention will be required - otherwise this is a purely mechanical process that's best left to the machine.

What append if the pictures are not available at the time or the order ?

However, in such case it is YOUR job to make sure ProductCode values are unique in the Products table. At the very least, set the field to validate as unique (this will also be reflected in the graph).

... an EventNumber field (in the LineItems table).

The next step depends on how many pictures will have a code that starts with 24...

The ProductCode will always be unique.

An EventNumber field (in the LineItems table). I have to think of that!

There will always be less than 99 pictures per event.

How will I display, count the pictures in the Picture table which have not been ordered (i.e no relationships)

There will always be less than 99 pictures per event.

In a relational database, there are only three numbers: zero, one and many.

How will I display, count the pictures in the Picture table which have not been ordered (i.e no relationships)

Count the related records in the Lineitems table.

Edited January 9, 201115 yr by comment

In a relational database, there are only three numbers: zero, one and many.

Count the related records in the Lineitems table.

Thanks Comment.

I will take the time to understand what you have been informing me with. This will take some time, it is a question of re-designing this application. I am running out of time as I have to have something ready for January 19. Then, the current design will be there to be operational for a short period of time. I will look at the demo solution in putting in place a more robust solution (long term)

Daniel

OK what I've done since the last time.

1. 
   
   
2. I've registered myself to Lynda.Com and I am going through all the videos. This is providing me with useful information and I am starting to get some of the FileMaker Pro fundamentals.
   
3. I've went through some of the sample models provided with FileMaker Pro
   
4. I've redone a ERD model and I've made changes accordingly in FileMaker Pro (see the link)
   
   

My new ERD model Please note I am including this link because I am unable to add a picture.

Relationships

1. A CommandeClient could have one to many SousCommande

2. A SousCommandeClient could have one to many CommandeOrdrePa

3. A CommandeOrdrePa could have one to many Produits

4. A Produits could be in one to many CommandeOrdrePa

Table CommandeOrdrePa

• 
  
  
• Status could be equal to {Commander | Filmer | Graver, | Livrer }
  

Table Produits

• 
  
  
• Status could be equal to blank or “Selectionner”
  

What is it that I want to do:

1. 
   
   
2. Select from CommandeOrdrePa all records with Status is equal to “Commander”
   
3. Select from CommandeOrdrePa and Produits all records with CommandeOrderPa:PictNo equal to Produits:PictNo
   
4. Replace related records in CommandeOrderPa field CommandeOrdrePa:Status to “Filmer”
   
5. Replace related records in Produits field Produits:Status to “Selectionner”
   

I've done the following script :

Go to Layout [ “CommandeOrdrePassage” (CommandeOrdrePassage) ]

Set Error Capture [On]

Perform Find [ Specified Find Requests: Find Records; Criteria: CommandeOrdrePassage::Status: “="Commander"” ]

[ Restore ]

If [ not Get(LastError) ]

Replace Field Contents [ Produits::Status; Replace with calculation: "Selectionner" ] [ No dialog ]

Replace Field Contents [ CommandeOrdrePassage::Status; Replace with calculation: If(CommandeOrdrePassage::Status = "Commander"; "Filmer"; CommandeOrdrePassage::Status) ] [ No dialog ]

Go to Related Record [ From table: “CommandeOrdrePassage”; Using layout: <Current Layout> ] [ Show only related records; Match found set ]

Constrain Found Set [ Specified Find Requests: Find Records; Criteria: CommandeOrdrePassage::Status: “="Filmer"” ] [ Restore ]

Go to Layout [ “CommandeClient” (CommandeClient) ]

Else

Go to Layout [ original layout ]

Beep

Show Custom Dialog [ Title: "Matching Products with CustmerOrderPa"; Message: "No records found"; Buttons: “OK” ]

End If

Go to Layout [ “CommandeClient” (CommandeClient) ]

Adjust Window [ Resize to Fit ]

Could someone tell me what is wrong with my script? One of the thing I could mention that I do not seem to have a way to relate records based on field values i.e CommandeOrderPa:PictNo equal to Produits:PictNo

I am continuing my investigation.

Best regards!

Daniel

To add a picture:

Step 1:

Step 2:

Step 3:

Your relationship graph (which is NOT an ERD)

ERD model.tiff

You are doing a great job of discussing all this in English, but I'm sorry to say that at least for me, it would help if you could translate your table names as well.

Hello Bruce, here it is !

ERD model.tiff

French to english:

CommandeClient : CustomerOder

SousCommande: SubOrder

CommandeOrdrePa: LineItem

Produits : Product

If you want me to change the table name in the diagram, let me know.

Relationships

1. 
   
   
2. A CustomerOrder could have one to many SubOrder
   
3. A SubOrder may have one to many LineItem
   
4. A Product could be in more than one LineItem
   

A SubOrder my have more than one Product

A Product could be in more than one SubOrder

This is why I've created the relationship LineItem (in order to remove the many to many relationship).

Regards!

Daniel

Quite helpful. Next question, what is the purpose of the script especially the replace operations?

I think this is going in a circle. The basic idea of a relational database is to eliminate the duplication of data. You have a Status field in no less than three tables - but I believe it has already been established that the only status that really changes is the status of the product.

The status of a line item is given by the status of the product. The status of an order can be calculated from the status of its line items.

I think it's closer to a spiral. At least it's being expressed as FileMaker operations. But the point of it all is far from clear.

The script is to relate two table together LineItem with Products by the field "PictNo"

Once related the replace field is to replace the Status field in the LineItem table from "Commander-Ordered" to "Filmed" and replace the Status field from nothing to "Selectionner-Selected"

I have a script which looks at the content of a directory and for all of the files found, the information about that file is imported in the Product table. One of the information is the PictNo. In the LineItem table all the pictures that have been ordered are listed there. Those orders in the LineItem table have the following status "Commander-Ordered, Filmed, Graver-Burned, Livrer-Delivered".

At this moment, I am only interested in matching the LineItem and the Product table for the records in the LineItem having their status equal to "Commander-Ordered". The common matching field is PictNo. All matching records will then get their Status changed. In the LineItem table it goes from "Commander-Ordered" to "Filmed". In the Product table it goes from nothing to "Selectionner-Selected".

Thanks!

Daniel

Well, maybe it's a downhill spiral.

Will you translate that again please. I'm not sure it's adding up, or that you've heard what Comment said.

The script is to relate two table together LineItem with Products by the field "PictNo"

Why?

Why doesn't the relationship do that - why aren't they already related?

Why have a script at all?

Why all these replace operations?