Grid in Filemaker

[wassimzu]

I have several products made up of several chemicals which are made up of several elements combined together. I need to made a grid (please see at the end)

Main Chemical 1 consist of Element 1, Element 2, Element 3

Main Chemical 2 consist of Element 2, Element 4

Main Chemical 3 consist of Element 3, Element 5, Element 6

 

Product 1 has Chemical 1, Chemical 2

Product 2 has Chemical 1, Chemical 3

Product 3 has Chemical 2

 

The layout for Chemical Table contains a portal for the related table Elements to input all the elements that made up the Chemical.

The layout Command has the Products Table

The layout Products has a portal for the related table Chemicals to input the chemicals that need to be used

The layout Command has a portal for the related table Products to input the Products

SO HERE IS MY APPROACH but if you have any other approach such as the use of a portal and “x” relationship please do tell me

I have created a field1 in the table Chemical that list all the process in each chemical, then another field2 in the table product that list all the process in field 1 and a simple custom function to remove the duplicates

Ex: for product 1 the list would display “Element 1, Element 2, Element 3, Element 2, Element 4”

With the custom function it displays “Element 1, Element 2, Element 3, Element 4”

I want to make a grid (as below) on the Layout Product when i print the specification of Product 1, my only problem is how do i insert the yes in the correct place

 

 

 

 

     

 

 

ALREADY MAKE THE LABELS USING PORTALS and filtering the duplicate value. The only problem is how to put the "yes"   ??

[comment]

I don't understand the "grid' you are showing us.

[bruceR]

He is trying to create a cross tab report.

[comment]

It's still very difficult to follow. For example, these two sentences seem to contradict each other:
 

The layout Command has the Products Table

...

The layout Command has a portal for the related table Products to input the Products

 

 

Try perhaps something along these lines:

 

1. Define a value list of "Elements" using values from field Elements::Name;

 

2. Define a calculation field in the Chemicals table =

Let (
label = GetValue ( ValueListItems ( Get ( FileName ) ; "Elements" ) ; Get ( CalculationRepetitionNumber ) )
;
not IsEmpty ( FilterValues ( label ; List ( Child::Child ) ) )
)

Set the result type to Number and the number of repetitions to the maximum number of elements you'd ever expect*. Format the filed to display as Boolean.

 

 

3. Define an unstored calculation field in the Products table =

GetValue ( ValueListItems ( Get ( FileName ) ; "Elements" ) ; Get ( CalculationRepetitionNumber ) )

Set the result type to Text and the number of repetitions to the same number as before.

 

 

4. Show the Boolean field in a portal to Chemicals, with repetitions laid out horizontally; use the Text field to display the labels above the portal.

 

 

 

--

(*) Filemaker is not flexible in the horizontal direction; the number of columns to be shown must be preset.

[LaRetta]

And on the Boolean field -  in Inspector > Data > Data Formatting set to Boolean and set non-zero values to YES and leave the zero or empty values empty.