Post
by Warwick Wilson » Sat Jun 02, 2018 7:41 am
Hi Sali,
Welcome. The amount of work I have to do on iFamily is like an iceberg and could sink ships. Not everything is always visible as there's always a lot of non programming work that distracts me from iFamily's true goals so please excuse any long breaks in conversation from my end.
iFamily was always built upon open standards to assist people to help themselves. The table names and fields get a little garbled with leading Z'ds but it's all available as shown below. Until I can realise your suggestion in the app, here's how to get the chronology report you desire:
1. Open Terminal and change into your iFamilyData folder.
2. type sqlite3 ROYALS92.FktSql
3. type .mode csv ZEVENT
4. type .output 'Reports/chronology.csv'
5. cut and paste:
select ev.ZNUMERICID, ev.ZDATE, ev.ZNAME, ev.ZPLACE, ev.ZNOTES, p.ZNUMERICID, p.ZNAME, p1.ZNUMERICID, p1.ZNAME, p2.ZNUMERICID, p2.ZNAME
from ZEVENT ev
LEFT JOIN ZPERSON p on ev.zperson = p.z_pk
LEFT JOIN ZPERSON p1 on ev.ZPERSON1 = p1.z_pk
LEFT JOIN ZPERSON p2 on ev.ZPERSON2 = p2.z_pk
order by ZYEAR, ZMONTH, ZDAY asc;
6. type .q
Your chronology report should then be present in your Reports folder. Let me know if I've missed any steps or if I am joining on the wrong columns, you may need to truth the id numbers before relying on the output. (the Z_PK Primary Key join may be incorrect and perhaps the ZNUMERICID columns should be the ones to join with.)
Some other things you may like to try while you are in the sql session are .tables and .schema ZEVENT. Let me know if I missed any steps.
Just in case you are tempted, pretend you never heard of the sql keyword "update", I don't recommend making any edits to the data except through the app itself. If you are tempted to try, please implement a rigorous backup regime.