I have tried a few of those options you suggested, but they are not working, I have other filters i need to apply as well, for instance i need it to filter by whether the supplier makes the part or just purchases it, whether the supplier is active or not, and if the Material number is blank inside of the part detail or not. I have tried the field and selfjoin that you have created in that example, but it shows 0s for all of the the values in the portal, after I filter by those other results.
Why don't you post a sample file that exemplifies your relationships?
Creating and applying a filter shouldn't be too complicated, but creating a file is tedious …
but it shows 0s for all of the the values in the portal, after I filter by those other results.
The result of that calculation field should in no way be dependent on other (or any) filters you apply to the portal. Something else must be off.
1. The Material Number in the join table is a calculation that references the material number in Parts – and thus is par force unstored and non-indexable. Therefore, you cannot use that field on the “target” side of a relationship, and your self-join fails. Use an auto-enter calc instead.
2. If you need additional (inclusion/exclusion) criteria, you must encode them in the relationship, not in the calculation; without that, the calculation will look for the Min() or Max() among all parts the same MTs, and what it finds isn't necessarily an active, non-purchased part.
3. You could use an SQL calculation similar to the one I suggested in the other thread, and forget about calcs and relationships.
The Problem with autoenter, is that we have the links created already before we have the material added. We add the materials as we determine them, and the materials can change for each part as well. would SQL work if I use the calculation I had?
I have attempted to use those, but for some reason I am still not getting any values in the portal.
That's too vague to make any suggestions, except to be aware that SQL reserved words and field names with leading underscores need to be double-quoted (which is why I renamed some fields for the sample file).
SQL has some peculiarities, and if you can't spend the time to dig a bit deeper into the subject, maybe this isn't the right approach for you.
On the other hand, this de-duplicating via filter is is all very interesting and can be useful, but I think in your particular case there's an easier solution. Filter out the non-suitable SupplierPart combinations and use the rest to look through a new relationship chain into Material:
Create a calc field in SupplierParts as
supplierID_applicable = Case ( not ( Status = "Inactive" or Type = "Purchased" ) ; ID_Supplier )
Create a new relationship from Suppliers to SupplierParts_applicable as
Supplier::ID = SupplierParts_applicable::supplierID_applicable
Now “look through” this chain of new TOs:
Supplier --< SupplierPart_applicable --< Part_applicable --< Material_applicable
What would you consider a leading underscore though? I know of some of the reserved words, and I was under the impression that they had been avoided in here. Also the database is in current use, and changing these fields would cause way to much of an issue, if calculations cannot be used. The tables I added are used through atleast another 10 tables in the database, so the fields cannot be changed without affecting the current setup of the database.
I can try the other solution, but I wanted to get the filter to work, instead of creating more TOs to be used.
I didn't say that leading underscores or reserved words are the cause for anything in your calculation, just to be aware of them as potential pitfalls. Also, you don't have to rename such fields to be able to use them with SQL, you can simply put them in quotes …
If you ask for assistance troubleshooting your calculation, why don't you post it?
I am tried to adjust the SQL you recommended, and when I change the values to what I have in the database, it shows no records at all. It is removing all of the records from the portal through the filter, instead of the blank and incorrect ones.
ExecuteSQL ( "
SELECT MIN ( SP.ID )
FROM SUPPLIERPART SP
JOIN PART P
ON SP.ID_Part = P.ID
SP.Status <> ? AND
SP.Type <> ? AND
SP.ID_Supplier = ?
GROUP BY P.MT_number
" ; "" ; "" ; "Inactive" ; "Purchased" ; SUPPLIER::ID
not IsEmpty ( FilterValues ( SUPPLIERPART::ID ; IDList ) )
I have tried removing certain lines from the SQL and I have been getting incorrect information then. It shows a record when I remove the join and group by from the SQL, but it then shows a blank record only, not all of them just only 1 blank record. I am really confused on why it would show just one record in the portal after the portal.
In such a case I find it best to totally deconstruct the formula and test its components. Doing that with yours showed that the innocuous looking statement
SELECT ID from Part
returns ? – because for some reason 'Part' isn't accepted. Wrap it into double-quotes, i.e. JOIN \"Part\" P … et voilà!
Don't know what's wrong with 'Part', though …
Thanks, that helped a lot, I wouldn't have thought to check the quotes for Part. I am still not used to the single and double quote for SQL. Do you have a recommendation of where to go to look into the single and double quotes? If so it would be helpful.