Portal, records, relationship?

[the_furious]

I'm working on a system that allows the head of each department to access and acknowledge monthly reports that are imported into the DB.

 

2 of the tables are as follows: (I've omitted other fields that are not required for this to work)

Records table: pk_recordID, date_imported, user_name, user_dept, acknowledge_date, acknowledge_avg, kf_reportID, g_deptcode (global field that is defined based on user login)

Reports table: pk_reportID

 

I'm currently using reports table as a portal to reflect the following fields:

Records::g_deptcode, Records::date_imported, and a button that displays a sub-summary list of records found based on Records::user_dept

 

I need some help for the following questions:

 

1) Displays records:acknowledge_date in my portal.

Let's say there's 2 department heads that access the same report/records, Head of HR may have acknowledged the records that reflects HR dept, I want the acknowledge_date to reflect for Head of HR but not Head of Finance who has not acknowledged the records for the same month.

 

2) Can I make records::kf_reportID into a secondary primary key(e.g. current last count of kf_reportID +1) on its own? so i can remove reports table and use records table as my portal instead.

(e,g. 100 records are imported during the month of dec'14. these 100 records shall hold the same kf_reportID)

 

Hope someone can enlighten me from here.

Thank you.

 

[the_furious]

no love? ):

[LaRetta]

Hi,

 

I suspect you are not getting responses because others are as confused by your request as I was.  Naming tables 'records' and 'reports' does not tell us their purpose.  Without their purpose, it is difficult to envision the type of data they contain and how they will be used.

 

"I'm currently using reports table as a portal to reflect the following fields:"

 

Your relationship seem backwards.  Where is the cardinality here?  Your portal should be based upon Records and not Reports.  I think it best if you can either zip and post your file or create a simple file showing the relationships and some sample data.  And name your tables the same as the type of data they contain, i.e. Invoices, Customers etc.  A reports table is rarely needed unless you need to perform some advanced reporting (which I do not see here), 

 

We will be happy to assist you once we are a bit clearer on your relationships.  :-)

 

Edited a sentence

[Lee Smith]

Hi the...,

 

Perhaps if you attach a copy of your file, we can see better what you need.

 

To attach a file, see this Link ATTACH FILE

 

Lee

[the_furious]

Your relationship seem backwards. Where is the cardinality here? Your portal should be based upon Records and not Reports. I think it best if you can either zip and post your file or create a simple file showing the relationships and some sample data. And name your tables the same as the type of data they contain, i.e. Invoices, Customers etc. A reports table is rarely needed unless you need to perform some advanced reporting (which I do not see here),

Hi LaRetta,

Records are all imported data from an excel file.

I need my portal to only display the date_imported, and not all records. Therefore, I created a separate report table to do so.

Is it possible for me to use records table as a portal to display non-repeated records like date_imported with user_dept?

Edit: LoginIDs can be Paul or John (no passwords)

the_furious_sample_data.zip

[LaRetta]

I had created a simple file, trying to get clarity on your issue.  Please see the attached zip, your file is backwards as I suspected.  Notice that the Reports table is not used (you can ditch it) and instead, I am using a self-join.  This only means that, in the graph, you duplicate your table and join it to itself.

 

Globals then can be used to select the Department and Import Date (they are set up as value lists to make it easier to select from).  The left set of selection globals in the header are based upon the single instance you wanted - a single Department and a single Import Date.  Notice that both the report and the portal below respond the same and produce the 'found set' using a script step called Go To Related Record[].

 

And then to the right is the same global fields but I used checkboxes so you could select multiple values from each.  To preserve the relationship, if no Departments are selected, auto-enter on Departments global relates them all; same with dates.  See if this moves you along a bit and then let us know what still isn't clear. :-)

You need to sign in to download the file.

Also, I didn't understand your quest about Acknowledgements so you'll have to clarify further - maybe in this file.

RecordsReports.fmp12.zip

[the_furious]

I had created a simple file, trying to get clarity on your issue.  Please see the attached zip, your file is backwards as I suspected.  Notice that the Reports table is not used (you can ditch it) and instead, I am using a self-join.  This only means that, in the graph, you duplicate your table and join it to itself.

 

Globals then can be used to select the Department and Import Date (they are set up as value lists to make it easier to select from).  The left set of selection globals in the header are based upon the single instance you wanted - a single Department and a single Import Date.  Notice that both the report and the portal below respond the same and produce the 'found set' using a script step called Go To Related Record[].

 

And then to the right is the same global fields but I used checkboxes so you could select multiple values from each.  To preserve the relationship, if no Departments are selected, auto-enter on Departments global relates them all; same with dates.  See if this moves you along a bit and then let us know what still isn't clear. :-)

You need to sign in to download the file.

Also, I didn't understand your quest about Acknowledgements so you'll have to clarify further - maybe in this file.

 

Hi LaRetta，

 

Thanks for the quick reply. Your file does help me lots.

 

As mentioned in my first post (question 2). I wanted to do away the Reports table but I need an identifier(besides date_imported) to segregate my records as there may be more than 1 import in a single day.

Should I change this date field to a timestamp instead?

 

The acknowledgement status/date will be flagged once the head of department has verified the report for the particular month for his department.

All records with the same date_imported & user_dept will have the same acknowledge_date.

So the same portal will display the acknowledge date to assist the head of department in his work.

[LaRetta]

Sure, make it a timestamp (both the Records::date_imported and the global).  The idea of the file is to show you how self-join works and filters work to segregate your data as you need it.

[the_furious]

Hi LaRetta,

 

There is something that doesn't quite fit in.

 

I'm trying to get the portal to display records of different months under the same department.

E.g. If there are 12 imports, once a month, the portal should display 

Jan'14 HR

Feb'14 HR

etc..

 

Like in my initial attachment.

[LaRetta]

It is no problem.  Just replicate what I have using month year instead of each timestamp (attached).  For a relationally-filtered portal, all that matters is that the data matches on both sides.  The 'parent' side (the table occurrence of the layout you are standing on) can be a global field, stored or unstored fields and the 'child' side (the table occurrence of your portal) must be stored values (your data fields).

 

Do not forget that you can also just perform a regular find for the month of records and then switch to your report layout.  The report layout, if used, must be sorted by the sub-summaries (read up on reporting in FM Help) for further assistance on reports.  The simple report I gave you allows for you to check in checkbox, all months for a full year (for example) and display those records not only in the portal, but in the report by month and by department.

 

There are many ways to configure a UI in this instance (and the accompanying functionality) ... only you will know what works best for your Users.

RecordsMoYr.fmp12.zip

[the_furious]

Hi LaRetta,

 

Sorry for the late reply. Just realised the field is my child table is unstored.

 

Thanks for the info.

 

 

Merry Christmas to you!