Two non SQL approaches that you can use.
Let's say that you have these tables and relationships:
Task::__pkTaskID = Tools::_fkTaskID
with a Layout based on Task and the portal on Tools.
Add an auto-entered serial number field or text field with Get ( UUID ) to uniquely identify each record in Tools.
Then set up this relationship:
Tools::ToolType = ToolsSameType::ToolType (These are two occurrences of the same table)
Then a portal filter defined as:
Tools::SerialNumberField = ToolsSameType::SerialNumberField
Will filter out the duplicate entries.
If the possible number of tool Types is fairly small, you can also use a table of tool types as the basis for your portal and set up what I call a "reach thru" relationship that reaches through it via a global field to match to the tools records specified for your current task:
Task::anyField X ToolTypes::anyField
ToolTypes::ToolType = Tools::ToolType AND
ToolTypes::GlobalTaskID = Tools::TaskID
The OnRecordLoad Script trigger would be used in this last case to update GlobalTaskID:
Set Field [ToolTypes::GlobalTaskID ; Task::__pkTaskID ]
Can you send me some example more for invice -< invoice_positions >- products
kind of example.
I have tried but after filtering only one or non portal rows show for me....
I would like to thank you for your support - sorry for late reply (business trip).
Small translation legen:
Zamowienie - order
poz_zamowienia - order_positions
produkty - products
1.png 69.4 K
Seems like I forgot to state the second "non SQL" option--to use a summary report in list view to list your portal items with a subsummary part but no body layout part. You simply sort your items by common value--such as product ID to get one row of data for each Product ID. Summary fields can then total up quantity, price etc.
Fields from the parent table would be added to the layout Header or a leading grand summary part.
So for your invoices, you'd set this layout by basing it on the same table occurrence that you'd normally select in portal set up as the basis for your portal and include fields from the related Invoice table in the header.
Please note that such a layout will always be blank when not sorted to include the subsummary part's sort field in the current sort order.
I use summary fields in higher list views without body - its grouping my invices by drivers and also in second layout by dates.
I dont understand this point:
"So for your invoices, you'd set this layout by basing it on the same table occurrence that you'd normally select in portal set up as the basis for your portal and include fields from the related Invoice table in the header."
Could you please be so kind and provide me some kind of example which I can open in Filemake and see how you do that?
1 of 1 people found this helpful
Here's a version working with portal filtering.
- added a calculated field "IloscTotal"in the positions table summing de amount by productID and invoiceID (didi with ExecuteSQL() but could be done with self relation).
- added a primary key "ID" to the positions table
- placed the "IloscTotal" over the "Ilosc" field in the portal (solid background), denied field entry and put a conditional formatting: text color = background color if t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::Ilosc = "" (as long there's no amount in a new record, the total field is invisible).
- finally the filter formula:
Let ( [
indexValues = List ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::ID ) ;
searchValues = List ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::_ProduktyIDfk ) ;
index = ValueCount ( Left ( indexValues ; Position ( ¶ & indexValues & ¶ ; ¶ & t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::ID & ¶ ; 1 ; 1 ) ) ) ;
prevValues = LeftValues ( searchValues ; index -1 )
IsEmpty ( FilterValues ( t05_b_zamowienie_T05_C_POZ_ZAMOWIENIA::_ProduktyIDfk ; prevValues ) )
Thanks for the example.
I've just did it by myself using this help article: The Self-Join Relationship: What it is and some examples of how to use it | FileMaker
Which helps me to filtrate by self-join relation and set up "1" in each order and each 1st occurrence of certain product.
(self-relation - see attachment)
Now I can filtrate my portal by "Dup_Spr" = 1 field and I will reach my goal.
Your example fits perfectly here because I will need to sum up if client will put qty in two separate Ilosc fields.
Thank you once again.
Coming back to the topic.
I have check all possibilities and to be honest. It would be much less confusing to my client if my value list of products will react to the Dup_Check flag from your file.
For example if someone pick already Tool1 it will not appear on the value list in drop-down list for this order.
I have already made a dynamic value list which changes together with group of "Tools" for certain client which is also connected to the same group. In simple words its results with value list which fit to the certain group.
But now additionally it should also react to the dup_check field....
Anyone have any idea how to solve it?
I agree, filter the value list and though not allow duplicate entries would be the better solution than filter out the duplicates. I recommend, that you search for MagicValueList technic to achieve this. This technic lets you define the value list entries by the onObjectEnter script trigger. The "AntiFilterValues" custom function from here: FileMaker Custom Function:AntiFilterValues( ListA; ListB ) could help to get the list entries like this:
Let ( [
_ListA = List ( Invoice_Products_global::ProductName ) ; //list of all products
_ListB = List ( Invoice_Positions::ProductName ) //list of already booked products
AntiFilterValues ( _ListA ; _ListB )
gives you a list with not yet used products