export or save layout data to excel

>>> I am having a problem saving or exporting data from a layout to an
>>> excel file. The data is correct on the screen but when I save it to an
>>> excel file, two of the four unstored, calculated fields are saved as a
>>> 0 or null.
>>> Any suggestions?
>
> Sorry... FM 9.
>
> Working calculated fields:
>
> ReportDate: If (tblMembershipDates::tblDatesLifeMember = 1; "Life
> Member" ; MonthName ( tblMembershipDates::tblDatesEndDate ) & " " &
> Year ( tblMembershipDates::tblDatesEndDate ) )
>
> Name1: tblMembers1::tblMembersMailName
>
> Non-Working Calculated Fields:
>
> Name2: tblMembers2::tblMembersMailName
>
> JournalName: Name1 & If(IsEmpty(tblMembers2::tblMembersMailName);"";
> " / " & Name2)
>
> Other info: tblMembers1 and tblMembers2 are self linked from
> tblMembers via a constant to select the proper names for each group.
>
> I'm not sure that the actual formula has much to do with my original
> question ... If everything appears correctly on the screen in a table
> format, shouldn't one be able to export it to an excel format file?
> But maybe the formula is what determines that.

You are probably correct in that what works on screen should also work
on export...probably maybe.

Are you performing an 'export records' to excel or a 'save as' excel?
On which table is the layout based from which you are performing the
export/save?

I'm not clear on your relationships, so I'm unable to reproduce what you
are doing. Are you saying there is a main tblMembers record, plus one
or two more related ones in the same table where additional names are
held? It seems your troubled calcs are the ones that include the
tblMembers2 table occurrence, so I'm trying to better imagine
specifically what this relationship is based on. If you would like to
directly send me a copy of your database, I would be happy to take a
look to determine whether there is a reasonable explanation for your
issue or whether perhaps it is a bug.

Thanks...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Member, FileMaker Business Alliance
 >> Stay informed about: export or save layout data to excel 

On Aug 16, 10:11 am, Howard Schlossberg
<how....RemoveThis@nospam.fmprosolutions.com> wrote:
> >>> I am having a problem saving or exporting data from a layout to an
> >>> excel file. The data is correct on the screen but when I save it to an
> >>> excel file, two of the four unstored, calculated fields are saved as a
> >>> 0 or null.
> >>> Any suggestions?
>
> > Sorry... FM 9.
>
> > Working calculated fields:
>
> > ReportDate:  If (tblMembershipDates::tblDatesLifeMember = 1; "Life
> > Member" ; MonthName ( tblMembershipDates::tblDatesEndDate ) & " " &
> > Year ( tblMembershipDates::tblDatesEndDate ) )
>
> > Name1:  tblMembers1::tblMembersMailName
>
> > Non-Working Calculated Fields:
>
> > Name2: tblMembers2::tblMembersMailName
>
> > JournalName: Name1 & If(IsEmpty(tblMembers2::tblMembersMailName);"";
> > " / " & Name2)
>
> > Other info: tblMembers1 and tblMembers2 are self linked from
> > tblMembers via a constant to select the proper names for each group.
>
> > I'm not sure that the actual formula has much to do with my original
> > question ... If everything appears correctly on the screen in a table
> > format, shouldn't one be able to export it to an excel format file?
> > But maybe the formula is what determines that.
>
> You are probably correct in that what works on screen should also work
> on export...probably maybe.
>
> Are you performing an 'export records' to excel or a 'save as' excel?
> On which table is the layout based from which you are performing the
> export/save?
>
> I'm not clear on your relationships, so I'm unable to reproduce what you
> are doing.  Are you saying there is a main tblMembers record, plus one
> or two more related ones in the same table where additional names are
> held?  It seems your troubled calcs are the ones that include the
> tblMembers2 table occurrence, so I'm trying to better imagine
> specifically what this relationship is based on.  If you would like to
> directly send me a copy of your database, I would be happy to take a
> look to determine whether there is a reasonable explanation for your
> issue or whether perhaps it is a bug.
>
> Thanks...
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg
> FM Professional Solutions, Inc.    Los Angeles
>
> FileMaker 8 Certified Developer
> Member, FileMaker Business Alliance

Howard,

1) It works the same if I "send/save as" or "export" the selected
data.

2) The layout is based on tblMembership.

3) perhaps this will help with the relationships.

tblMembership holds information such as membership Number (ID),
address, type of membership, membership status of a memberhip. Each
membership may have up to two members, depending upon the type of
membership.

tblMembers holds the individual's information such as name, order
within the membership, email, phone, tblMembershipID.

tblMembership is linked to tblMembers1 and tblMembership is linked to
tblMembers2. like the following:

tblMembership condition tblMembers1

tblMembershipID = tblMembersMembershipID

tblMembershipg1
(global constant '1') = tblMembersOrder

tblMembers1 and tblMembers2 are additional instances of tblMembers.

Perhaps this will help the relationship.

I appreciate your offer to look at the database. Since it is a live
database and contains personal information of the members, I will have
to make a copy of the structure and add a few, fake records to
demonstrate the problem before sending it to you. Please send me
privately the address of where to send the database copy; I believe my
return address is can be seen above.

By the way, is there a utility that will show me the instances of a
field / table / layout within the database? Later I want to clean up
some 'inherited' items from the database and do not want to accidently
clobber something.

I am running FM 9 Pro on Mac Leopard.

Thanks for your help.
 >> Stay informed about: export or save layout data to excel 

It would help if you gave both the non-offending and the offending
calculations, and include FMP version and platform.

Keep well, Ursus

<d.kinnard.DeleteThis@sbcglobal.net> schreef in bericht
news:ca54d851-70a6-4d1c-8e05-edea6bea545c@a2g2000prm.googlegroups.com...
>I am having a problem saving or exporting data from a layout to an
> excel file. The data is correct on the screen but when I save it to an
> excel file, two of the four unstored, calculated fields are saved as a
> 0 or null.
>
>
> Any suggestions?
 >> Stay informed about: export or save layout data to excel 

On Aug 16, 3:07 pm, Helpful Harry <helpful_ha....DeleteThis@nom.de.plume.com>
wrote:
> In article
> <ca54d851-70a6-4d1c-8e05-edea6bea5....DeleteThis@a2g2000prm.googlegroups.com>,
>
> d.kinn....DeleteThis@sbcglobal.net wrote:
> > I am having a problem saving or exporting data from a layout to an
> > excel file. The data is correct on the screen but when I save it to an
> > excel file, two of the four unstored, calculated fields are saved as a
> > 0 or null.
>
> > Any suggestions?
>
> If the data loks correct on-screen, but Exported data is different,
> then my first guess would be that you're simply exporting the wrong
> fields. Check the fields in Layout mode to make sure they are the ones
> you think they are - the correct names coming from the correct Tables.
>
> Helpful Harry                  
> Hopefully helping harassed humans happily handle handiwork hardships  ;o)

The names appear to be correct and coming from the correct files. The
troublesome fields are in only the base table. All fields appear
correct in Form, List, and Table Views. The export or save as have
problems with two fields.

As an academic question, should one not theoretically get in the saved
as file the data that one sees on the screen as a table?

Dan
 >> Stay informed about: export or save layout data to excel 

d.kinnard.DeleteThis@sbcglobal.net wrote:
> On Aug 16, 3:07 pm, Helpful Harry <helpful_ha....DeleteThis@nom.de.plume.com>
> wrote:
>> In article
>> <ca54d851-70a6-4d1c-8e05-edea6bea5....DeleteThis@a2g2000prm.googlegroups.com>,
>>
>> d.kinn....DeleteThis@sbcglobal.net wrote:
>>> I am having a problem saving or exporting data from a layout to an
>>> excel file. The data is correct on the screen but when I save it to an
>>> excel file, two of the four unstored, calculated fields are saved as a
>>> 0 or null.
>>> Any suggestions?
>> If the data loks correct on-screen, but Exported data is different,
>> then my first guess would be that you're simply exporting the wrong
>> fields. Check the fields in Layout mode to make sure they are the ones
>> you think they are - the correct names coming from the correct Tables.
>>
>> Helpful Harry
>> Hopefully helping harassed humans happily handle handiwork hardships ;o)
>
> The names appear to be correct and coming from the correct files. The
> troublesome fields are in only the base table. All fields appear
> correct in Form, List, and Table Views. The export or save as have
> problems with two fields.
>
> As an academic question, should one not theoretically get in the saved
> as file the data that one sees on the screen as a table?

Theoretically, maybe. Well, if all fields are in the layouts table,
yes. If some of the export fields are in the related table, then you
will get some rows that are blank other than the related fields. Not
what you are seeing, but an exception to the rule nonetheless. If what
you are seeing is a bug, then I'd be happy to test it and report it to
FileMaker, Inc as a bug so that it gets fixed in the next version.
Since I couldn't replicate the issue in my test file, it is really hard
to say what or why it is happening.
 >> Stay informed about: export or save layout data to excel 

On Sat, 16 Aug 2008 00:17:15 -0700 (PDT), d.kinnard.RemoveThis@sbcglobal.net
wrote:

>I am having a problem saving or exporting data from a layout to an
>excel file. The data is correct on the screen but when I save it to an
>excel file, two of the four unstored, calculated fields are saved as a
>0 or null.
>
>
>Any suggestions?

Excel has driven me nuts doing this for years. The only reliable way
around it I know of is:

1) Export your data from FMP to CSV format.
2) Open the CSV file in Excel ... BUT don't use File-}Open. Instead
just drag the CSV file onto Excel and drop it. This opens a screen
where you can make some choices about field formating and delimiters.
3) Set the target field as type text and you're good to go.

hope this helps

--
FW


FileMaker Pro 8.5 Advanced on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2
 >> Stay informed about: export or save layout data to excel 

On Aug 17, 2:53 am, ma... RemoveThis @invivo.edu (Philippe Manet) wrote:
> <d.kinn... RemoveThis @sbcglobal.net> wrote:
> > The data is correct on the screen but when I save it to an
> > excel file, two of the four unstored, calculated fields are saved as a
> > 0 or null.
>
> result of calculated field can be affected as text, date or number.
> If you chossed number but the result is text, the result is good on
> screen but export as a number gives null.
>
> maybe.
>
> Philippe

Philippe,

You are absolutely correct. I failed to change the type on those two
calculations to text (the default seems to be number). Changing the
type seems to have solved the problem.
 >> Stay informed about: export or save layout data to excel 

On Aug 17, 3:30 am, FastWolf <wolfsof... RemoveThis @NOSPAMcomcast.net> wrote:
> On Sat, 16 Aug 2008 00:17:15 -0700 (PDT), d.kinn... RemoveThis @sbcglobal.net
> wrote:
>
> >I am having a problem saving or exporting data from a layout to an
> >excel file. The data is correct on the screen but when I save it to an
> >excel file, two of the four unstored, calculated fields are saved as a
> >0 or null.
>
> >Any suggestions?
>
> Excel has driven me nuts doing this for years.  The only reliable way
> around it I know of is:
>
> 1) Export your data from FMP to CSV format.
> 2) Open the CSV file in Excel ... BUT don't use File-}Open.  Instead
> just drag the CSV file onto Excel and drop it.  This opens a screen
> where you can make some choices about field formating and delimiters.
> 3) Set the target field as type text and you're good to go.
>
> hope this helps
>
> --
> FW
>
> FileMaker Pro 8.5 Advanced on Windows XP Pro SP2
> FileMaker Server 8.0 on Windows 2003 Server R2

FW,
This also seems to work well. You can also bring the csv into Numbers
and save it as xls.

Thanks for your help.
 >> Stay informed about: export or save layout data to excel 

Thanks to all who gave input on MY problem.

The problem was the type of the calculation on those two fields. All
seems to be fine now.

This brings about another, possible unanswerable, question:

Why would the data be shown one way on the screen and another way when
exported? Are not really looking at what is in the file?

Thanks again to all who helped.

Dan
 >> Stay informed about: export or save layout data to excel 

In article
<ca54d851-70a6-4d1c-8e05-edea6bea545c.TakeThisOut@a2g2000prm.googlegroups.com>,
d.kinnard.TakeThisOut@sbcglobal.net wrote:

> I am having a problem saving or exporting data from a layout to an
> excel file. The data is correct on the screen but when I save it to an
> excel file, two of the four unstored, calculated fields are saved as a
> 0 or null.
>
> Any suggestions?

If the data loks correct on-screen, but Exported data is different,
then my first guess would be that you're simply exporting the wrong
fields. Check the fields in Layout mode to make sure they are the ones
you think they are - the correct names coming from the correct Tables.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
 >> Stay informed about: export or save layout data to excel 

<d.kinnard RemoveThis @sbcglobal.net> wrote:

> The data is correct on the screen but when I save it to an
> excel file, two of the four unstored, calculated fields are saved as a
> 0 or null.

result of calculated field can be affected as text, date or number.
If you chossed number but the result is text, the result is good on
screen but export as a number gives null.

maybe.

Philippe
 >> Stay informed about: export or save layout data to excel 

<d.kinnard.DeleteThis@sbcglobal.net> wrote:

> Why would the data be shown one way on the screen and another way when
> exported?

one special feature of FMP (vs other SGDB) is that you can enter text in
number fields This can be useful, but may become dangerous in some
instances. Anyway, it has to be kept in memory.
 >> Stay informed about: export or save layout data to excel 

<d.kinnard.TakeThisOut@sbcglobal.net> wrote in message
news:33c8d5bd-e921-4ad2-87ad-c53905f4060e@b2g2000prf.googlegroups.com...
> Thanks to all who gave input on MY problem.
>
> The problem was the type of the calculation on those two fields. All
> seems to be fine now.
>
> This brings about another, possible unanswerable, question:
>
> Why would the data be shown one way on the screen and another way when
> exported? Are not really looking at what is in the file?
>
> Thanks again to all who helped.
>
> Dan
>
No
Very briefly data in memory or in the file is just a binary number and is
formated according to the field type on the current screen and can be
displayed differently in different layouts or even different fields on the
same layout.

Example -- A Date field can be shown in various date forms or just the
number of seconds that is how it is stored.

John G.
 >> Stay informed about: export or save layout data to excel