Making a query joining two FMP tables together

[comment]

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.

[Daniel Paquin]

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)

[comment]

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, 2011 by comment

[Daniel Paquin]

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

[Daniel Paquin]

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

[bruceR]

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.

[Daniel Paquin]

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

[bruceR]

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

[comment]

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.

[bruceR]

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.

[Daniel Paquin]

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

[bruceR]

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.

[bruceR]

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?