I am trying to get a count for the number of records based on two fields.
Field 1: Item Code
Field 2: Artist Code
I am not really certain how to go about this.
Any help would be great!
Define a "count of" summary field. Set it to count any field that is never empty, such as Item code.
Set up a summary report with a pair of sub summary parts "when sorted by", Item code and Artist code. Put this one summary field in both sub summary parts.
Sort your records by Item code and artist code. The totals for each will appear in the sub summary parts.
There are many other ways to get such counts and I am guessing a bit as it as to what totals you want to get as your post could just as easily be interpreted that you want two completely independent counts--which is also possible.
Sorry I just realized I didnt ask the question correctly.
What I am trying to do is get a total count of records that with a certain status.
Table: Piece List
Field 1: Costume Item code, Field 2: Artist Code Field 3: Item status
Field 1 and 2 reference a certain artist and certain costume item for which the artist could have several of the same item with different item status.
Table 2: Costume forecast
Field 1: Costume Item code, Field 2: Artist Code Field 3: total active items
Is there a way to have it count the records with out having to have a sub summary or a sorting of any kind?
not certain if it makes sense.
I'm still guessing here
I think that from a layout based on Costume Forecast, you want a count of the number of records in Piece List with a matching field 1 and field 2. But I don't get what role "status" plays in this.
If you define a relationship matching on both of these two fields, a calculation field in Costume Forecast can be defined to use: Count ( PieceList::costume item code ) to count the number of matching records.
ExecuteSQL could also be used without needing to first define the relationship.
The status is important because I need to know which items are active and which ones are only stock.
So ideally what I want is:
Costume item code: 818213, Artist code: 123253, Item status: Active, Item inv#: MJ123456
Costume item code: 818213, Artist code: 123253, Item status: Active, Item inv#: MJ123457
Costume item code: 818213, Artist code: 123253, Item status: Stock, Item inv#: MJ123459
Total number of Active items for Costume item code: 818213, Artist code: 123253 = 2
Total number of Stock items for Costume item code: 818213, Artist code: 123253 = 1
Does that make more sense? Sorry have trouble putting it into words.
Ok that makes this yet another "sum if" or "selective sum" question. It's a fairly frequently asked question.
There are multiple options that can produce such counts.
The summary report, which can be popped up in its own window is one option.
The method that I just described can be modified to produce these counts if you use two occurrences of the PieceList table and include fields for the status. On the forecast side, you add calculation fields that return a specific status. The relationships then match by all three values and the count functions work.
You can also stick with the original relationship but set up two unfiltered one row portals. The portal filter specifies the status and a summary field from piece list produces the counts.
And ExecuteSQL is still an option for these subtotals.
Thank you for all of the advice! I have a much better direction to head in. I have never used ExecuteSQL before so this will be a learning curve!
Here is what I have come up with but it doesn't seem to work. I just get a ? in the field
ExecuteSQL ("SELECT COUNT (_fk_Artist_SAP_Number & _fk_Costume_Item_SAP_Number) FROM CostumeItemDetails WHERE Costume_Item_Status = Live-Active GROUP BY _fk_Artist_SAP_Number, _fk_Costume_Item_SAP_Number ";"";"")
And it can be a steep learning curve ;-)
Maybe this. I can't tell all the correct table occurrence and field names to use nor what is the correct value for "active", but presume that it is text and should be enclosed in single quotes as a result.
ExecuteSQL ( "
SELECT Count ( \"_fk_Artist_SAP_Number\" ) FROM PieceList
WHERE \"_fk_Costume_Item_SAP_Number\" = ? AND
\"_fk_Artist_SAP_Number\" = ? AND
Costume_Item_Status = 'Active' " ;
"" ; "" ; CostumeForecast::_fk_Costume_Item_SAP_Number ; CostumeForecast::_fk_Artist_SAP_Number )
You cannot use the "&" concatenation that way inside the eSQL statement. You can create a calculation field (define field) and use that in the query as a single column/field. However, an 'unstored' may be slower.
Ok so I created a merge field if you will that combines the Costume Item SAP number and the Artist SAP number which has helped. I was able to get the SQL calculation below to work however it only counted the Live-Active items.
ExecuteSQL ("SELECT COUNT (CID_CISAP_ASAP) FROM CostumeItemDetails WHERE Costume_Item_Status = 'Live-Active'";"" ;"")
I have realized that what I need for this to do to get the results I am looking by matching certain info.
So when I am in the Costume Requirements table I need it to read the the merge field, then find those records that match in the CostumeItemDetails table which have the status of "Live- Active" to give me the count that I am looking for. Sorry this is such a pain!
I truly appreciate all of the help and guidance!
You could use execute SQL to set several such merge variables and just change the WHERE criteria in each query to specify a different status.
ExecuteSQL ("SELECT Costume_Item_Status, _fk_Artist_SAP_Number, _fk_Costume_Item_SAP_Number, COUNT (*) FROM CostumeItemDetails GROUP BY Costume_Item_Status, _fk_Artist_SAP_Number, _fk_Costume_Item_SAP_Number ";"";"")
SELECT Costume_Item_Status, _fk_Artist_SAP_Number, _fk_Costume_Item_SAP_Number, COUNT (*)
GROUP BY Costume_Item_Status, _fk_Artist_SAP_Number, _fk_Costume_Item_SAP_Number
When use COUNT() function in SELECT list, non aggregated fields need to be in GROUP BY clause.
See the reply (above?):
The GROUP BY will 'gather' the records/rows together so that you will have all of them, but "summarized".
Retrieving data ...