Jump to content

Recommended Posts

Hi Guys, 

 

I'm back, with some final (hopefully) questions on Reporting. Assuming the Data looks like this:  

image.png.dc41d4f3a8a2aa184d6398bfe0b01d3f.png

I can create Reports from the Item, Type, etc that's easy. 

What I'm struggling with is the following: 

1. Making a Report which is sorted by Type, but only where the Qty is above 0. So where Bread, Cheese and Crackers would be excluded.

 

2. Making a Report which shows the 'top 10' owned, and 'bottom 5' owned - based on qty field.

 

3. Making a Report which shows me the 'best' month (a) per product and [which month did I have the most of all items] and (b) the same in year. 

 

I'd appreciate any help or advice :)

 

Thanks!

N 

 

 

 

 

image.png

Link to post
Share on other sites
  • Replies 7
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

Posted (edited)
5 hours ago, Neil Scrivener said:

1. Making a Report which is sorted by Type, but only where the Qty is above 0.

Assuming that quantity cannot be negative, the only way that a quantity of a group can be 0 is if all the quantities in the group are 0. So simply start by performing a find for quantity > 0.

 

5 hours ago, Neil Scrivener said:

2. Making a Report which shows the 'top 10' owned,

I assume you mean the top 10 types by quantity? For this, you will need to first sort your records by Type and then omit any records after the 10th group. This can be done quickly using an adaptation of a method called Fast Summaries by Mikhail Edoshin.

Your table needs to have 2 summary fields: sTotal for totaling the quantity and sCount that counts some field that cannot be empty (e.g. ItemID).

Start by finding the items iyou want to report on and sorting them by Type, reordered by sTotal, descending. This groups the items by type and places the groups with the highest quantities at the top of the report.

Then make your script do:

# GO TO FIRST RECORD OF EACH GROUP, USING "FAST SUMMARIES"
Go to Record/Request/Page [ First ]
Loop
  Set Variable [ $i; Value:$i + 1 ]
  Set Variable [ $nextRecord; Value:Get ( RecordNumber ) + GetSummary ( Items::sCount ; Items::Type) ]
  Exit Loop If [ $nextRecord > Get ( FoundCount ) or $i > 10 ]
  Go to Record/Request/Page [ $nextRecord ] [ No dialog ]
End Loop
# OMIT EXCESS RECORDS
If [ $i > 10 ]
  Omit Multiple Records [ Get ( FoundCount ) - Get ( RecordNumber ) + 1 ][ No dialog ]
End If

 

I am afraid I did not understand #3. It sounds like there are several questions in there and each deserves its own thread.

 

Edited by comment
Link to post
Share on other sites

Thanks for this will give a go. 

Should I be creating a Script to run the Find, so that if additional value are created - or that the Find gets reset, it can be re-instated? 

Or is it the case that once Find has been activated, it's 'locked in'? 

Link to post
Share on other sites

I am not sure I understand the question. You said you wanted to produce a report. A script that produces a report will normally start by establishing a found set of the records to report on. 

 

Link to post
Share on other sites

Thanks for this, I've ran into two problems: 

The first is that when using Find, inserting >18 I'm getting a message saying "No records match this criteria". However >0 works perfectly. Why is this? 

The second is that I have a nicely laid out report when sorted by Type. I have a sub-summary with with Type heading, and then the Body contains the vegitables and quantities. When performing a Find (as per the working one, above), my lovely titles and headings are vanishing, and I'm getting hard and fast data. I guess this is because Find it showing 'Body' only - but when creating a Report (say a paper of PDF), I'd want to perform the Find, and then keep the nicities of my Header, Sub-Summary, Footer etc.... How would I do that? 

Thanks!

 

 

 

Link to post
Share on other sites

Got it! Thanks. 

For anyone else reading who experiences the same issues - I believe you need to: 

1. Perform the Find in your Criteria

2. Sort Records 

I'm going to make a script that does this, and then when clicked takes you to the Print option - which should package everything nicely up :)

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By GAltanis
      Dear users,
      I have three tables for three relevant layouts. Each one has separate table and tables are related with only one field.
      The scope is to show on the 2nd and 3rd layout the records that related with the 1st one. Here are some screen shots with what I need to do. Actually, I need to have on the seconf layout ("Orders from suppliers") the related records from the first one ("Suppliers list")
      Thank you thank you
      Giorgos

       

    • By epatrick
      I find it odd that FileMaker is so intuitive yet hides access to the file name of the data source during importing. It looks like the only way I have found based on posts on the forum is to import the Data Source file as a reference into a container field. That is an extra step that shouldn't have to be done since FileMaker sees the Source file name during multiple points in the import process. Here are three dialogs where it's seen during import. There has to be a way to use the input file name with some kind of Get Function.  Please help!

    • By 34South
      I previously used ODBC Manager (32 bit)  to great success importing data directly from Filemaker Server to JMP. I recently upgraded to Catalina (MacOS 10.15.5) and knew that one of the casualties would be this ODBC utility. I downloaded the 64 bit ODBC manager from Actual Technologies and successfully installed it but get the following message when trying to open an FM database from within the ODBC interface in JMP:
      dlopen(/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so, 6): image not found
      I have navigated to Actual Technologies' web site believing I should download an ODBC driver but this comes at a hefty price tag, especially when converted to my local currency. Given the increasing costs of maintenance contracts and SSL certificates I had hoped to avoid further expenditure. Do I really need this and is there an alternative?
    • By droid
      I've been saving various files - mostly pdfs - in FM container fields, for years. A script triggered by clicking in the field allowed the field to be exported for viewing.
      Recently I upgraded to FM18, and now when I click in the field, I'm told "container fields cannot be exported"!
      Is there a new way that I should be doing this? Thanks.
    • By ggt667
      I can ping my PDF document server from Terminal, I can connect to the PDF document server from all browsers apart from Safari, my default web browser is FireFox, I also tried to change to Chromium and Opera as the default web browser. WebViewer has the same symptoms as Safari, server not found.
      $ ping -nc 1 document PING document (10.0.0.7): 56 data bytes 64 bytes from 10.0.0.7: icmp_seq=0 ttl=64 time=0.002 ms --- document ping statistics --- 1 packets transmitted, 1 packets received, 0.0% packet loss round-trip min/avg/max/stddev = 0.002/0.002/0.002/0.000 ms FileMaker says ’Couldnot connect to the server.’
       
      The issues is easily solved by creating a new MacOS X user, and log in to that user, however I would rather like to fix the current user not having to migrate all other application settings. Is there some dns cache specific to Web Viewer and Safari?
  • Who Viewed the Topic

    1 member has viewed this topic:
    Bob Minteer 

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.