Making a query joining two FMP tables together

[Daniel Paquin]

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

[comment]

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.

[CobaltSky]

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.

[Daniel Paquin]

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

[comment]

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.

[Daniel Paquin]

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.

[comment]

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?

[Daniel Paquin]

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.

[comment]

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.

[Daniel Paquin]

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

[comment]

I am afraid you have lost me...

[Daniel Paquin]

Hello Comment

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

Thanks!

Daniel

[Lee Smith]

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

[comment]

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

[Daniel Paquin]

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

[comment]

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 Paquin]

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!

[Daniel Paquin]

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

[comment]

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?

[Daniel Paquin]

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

[CobaltSky]

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

[comment]

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.

[Daniel Paquin]

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

[Daniel Paquin]

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.

[Daniel Paquin]

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"

[comment]

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

[Daniel Paquin]

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

[comment]

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

[Daniel Paquin]

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.

[comment]

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.

[Daniel Paquin]

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.

[comment]

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.

[Daniel Paquin]

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?

[comment]

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.

[Daniel Paquin]

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.